# Calculated table question

• ### 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.

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

• 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]))

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

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]))

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

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