topic Re: Relate tables results in DAX from 2 different tables that are not related in DAX Commands and Tips

Relate tables results in DAX from 2 different tables that are not related

Forrestgump1980 — Fri, 05 Jul 2024 06:07:17 GMT

I have 2 tables that I cannot link because it will create a many to many relationship. 1 Table is called 'Requisitions' the other is called 'New Hires'. Both tables have a field 'Division'. In the 'Requisition' table I can count the number of requistions in a pivot by division, in the 'New Hires' table I can count the number of new hires in a pivot separately. However, I want to combine the results in 1 table.

When I try and bring in the results from the 'New Hires' table to the 'Requisition' table using CountRows("New Hires") it brings back the total 'New Hires' in each row i.e. 5,270 repeated in each division. but what I want is the count to count the number of new hires in each division respectively.

I am currently trying to create something using the functions CALCULATED, VALUES, FILTER but i can't get anything to work at the moment. I am not even sure this is the correct approach.

Any help is greatly appreciated!

Re: Relate tables results in DAX from 2 different tables that are not related

bhanu_gautam — Fri, 05 Jul 2024 06:23:03 GMT

Hi Forrestgump1980 , you can use DAX measures and the CALCULATE function along with FILTER to get the counts for each division

First measure

RequisitionCount = COUNTROWS('Requisitions')

Secondmeasure

NewHiresCount = CALCULATE(
COUNTROWS('New Hires'),
FILTER('New Hires', 'New Hires'[Division] = MAX('Requisitions'[Division]))
)

Drag the Division field from the Requisitions table to the Values area of the table visualization.
Drag the RequisitionCount measure to the Values area.
Drag the NewHiresCount measure to the Values area.

Re: Relate tables results in DAX from 2 different tables that are not related

Forrestgump1980 — Fri, 05 Jul 2024 06:41:58 GMT

Thanks for this.... I might be something wrong but now the counts are working across the division and are working correctly but unfortunately the total doesn't add up to counts in the NewHiresCount??

Re: Relate tables results in DAX from 2 different tables that are not related

bhanu_gautam — Fri, 05 Jul 2024 06:48:20 GMT

Forrestgump1980 , This might be context issue, use this measure

NewHiresCount =
SUMX(
VALUES('Requisitions'[Division]),
CALCULATE(
COUNTROWS('New Hires'),
FILTER('New Hires', 'New Hires'[Division] = EARLIER('Requisitions'[Division]))
)
)

Re: Relate tables results in DAX from 2 different tables that are not related

Forrestgump1980 — Fri, 05 Jul 2024 07:15:05 GMT

Thank you so much for your help that worked. I still need to add some filters to the final solution but i will try that on my own first. Thanks once again!