locked
Calculated table question RRS feed

  • Question

  • Hello,

    I've got two tables, one that has a column which contains active employees, and the other has a list of quizzes that employees have taken(The entries are completed quizzes and if an employee hasn't completed the quiz it will not show up).

    I would like to create a calculated table that shows whether each employee completed the quiz.

    (I made this visual in excel because it was easier, but am looking for the solution in dax)

    My expected result is the calculated table in this visual. Basically if the entry exists in Table 2 then the third column in the new table will say Yes and No if it doesn't exist in table 2. I won't have an issue making the third column, but I'm having trouble creating the calculated table.

    Thanks in advance.


    • Edited by jshinnenkamp Wednesday, January 31, 2018 9:07 PM
    Wednesday, January 31, 2018 9:03 PM

Answers

  • You should just be able to crossjoin the distinct employees and distinct quizes to create the first two columns of your calculated table

    eg.

    CROSSJOIN( DISTINCT(Table1[Employee]), DISTINCT(Table2[Quiz]))


    http://darren.gosbell.com - please mark correct answers

    Wednesday, January 31, 2018 9:42 PM
  • Hi jshinnenkamp,

    Thanks for your question.

    You can create the calculated table as Darren posted.

    Table =
    CROSSJOIN ( DISTINCT ( Table1[Employee] ), DISTINCT ( Table2[Quiz] ) )

    Then you can create a calculated column called Flag as below:

    Flag =
    IF (
        ISBLANK (
            LOOKUPVALUE (
                Table2[Quiz],
                Table2[Employee], 'Table'[Employee],
                Table2[Quiz], 'Table'[Quiz]
            )
        ),
        "NO",
        "YES"
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, February 1, 2018 5:49 AM

All replies

  • You should just be able to crossjoin the distinct employees and distinct quizes to create the first two columns of your calculated table

    eg.

    CROSSJOIN( DISTINCT(Table1[Employee]), DISTINCT(Table2[Quiz]))


    http://darren.gosbell.com - please mark correct answers

    Wednesday, January 31, 2018 9:42 PM
  • Hi jshinnenkamp,

    Thanks for your question.

    You can create the calculated table as Darren posted.

    Table =
    CROSSJOIN ( DISTINCT ( Table1[Employee] ), DISTINCT ( Table2[Quiz] ) )

    Then you can create a calculated column called Flag as below:

    Flag =
    IF (
        ISBLANK (
            LOOKUPVALUE (
                Table2[Quiz],
                Table2[Employee], 'Table'[Employee],
                Table2[Quiz], 'Table'[Quiz]
            )
        ),
        "NO",
        "YES"
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, February 1, 2018 5:49 AM