topic Re: Dax - Show difference between two related columns in different tables in DAX Commands and Tips

Dax - Show difference between two related columns in different tables

abzt — Mon, 22 May 2023 12:28:27 GMT

I have two columns of data for Emplolyees - linked via Employee ID

Table 1 - Shows the employeees who have booked on for a particular date range (Emp ID/ First Name/ Last Name/Work Date/Hours)

Tabel 2 - Is the listing of ALL company employees (Emp ID/ First Name/ Last Name)

Outcome - I am trying to get an output of the employees from Table 2 that do not feature in the Table 1 lisitng for the chosen sliced date range

Re: Dax - Show difference between two related columns in different tables

johnt75 — Mon, 22 May 2023 12:52:58 GMT

Use the columns from table 2 in the visual and add a filter so that 'Table 1'[Employee ID] is blank.

Re: Dax - Show difference between two related columns in different tables

abzt — Mon, 22 May 2023 13:15:45 GMT

Thank you for your response, following those steps however there are no blank values displaying in the filter listing for the visual as an option or returning if I choose "is blank" , I know I am expecting 40 records

Re: Dax - Show difference between two related columns in different tables

johnt75 — Mon, 22 May 2023 13:22:13 GMT

You could create a measure as COUNTROWS('Table 1') and filter for values >= 1.

Re: Dax - Show difference between two related columns in different tables

abzt — Mon, 22 May 2023 13:39:10 GMT

I'm not sure what Im doing wrong but I can't get to a list of those who have not booked on, the measure only shows those who have booked again i cant get to the blanks

Re: Dax - Show difference between two related columns in different tables

johnt75 — Mon, 22 May 2023 13:51:12 GMT

Just for debugging try creating a new table like

Tmp table = CALCULATETABLE ( EXCEPT ( VALUES ( 'Table 2'[Employee ID] ), VALUES ( 'Table 1'[Employee ID] ) ), DATESBETWEEN ( 'Date'[Date], DATE ( 2023, 1, 1 ), DATE ( 2023, 1, 31 ) ) )

replacing the start and end dates with what you have chosen from the slicer.

Re: Dax - Show difference between two related columns in different tables

abzt — Mon, 22 May 2023 14:01:25 GMT

That seems to display the values I am looking for on the non-bookers, how can I incorporate that into the report?

Re: Dax - Show difference between two related columns in different tables

johnt75 — Mon, 22 May 2023 14:15:46 GMT

Create a measure like

Emp is visible = IF ( SELECTEDVALUE ( 'Table 2'[Employee ID] ) IN EXCEPT ( VALUES ( 'Table 2'[Employee ID] ), VALUES ( 'Table 1'[Employee ID] ) ), 1, 0 )

and add that as a visual level filter to only show when the value is 1

Re: Dax - Show difference between two related columns in different tables

abzt — Mon, 22 May 2023 14:42:20 GMT

Thank you very much I now have the desired results