Count Hires from One Table based on criteria from another

Hi, I have two tables, an Agency Invoice Table and a Hires Table. Both tables have mutliple key identifies that I'm trying to use to connect the two tables together to show the number of hires fo...

General Comment

Need Help

Show and Tell

Tips and Tricks

Tutorial Requests

Watsky
4 years ago
Hey dreaves1,

A many to many is not the best way. Instead, create a bridge table. Referencing one of the tables.

Remove all the columns except Agency Name.

Append the other table to this referenced one.

Remove all columns except for Agency Name.

Remove duplicates

Then close and apply. Join Agency Table and Hires Table to the Bridge Table. You'll need to set at least one of the joins to a cross-filter direction of both.

Check your results then. You may find that you have to set both to a cross-filter direct of both.

Watsky

Solution Sage

4 years ago

Hiya dreaves1 ,

I setup your scenario by joining the two Agency name fields:

I wrote out this DAX formula:

Total Hires = 
VAR Agency =
    VALUES( 'Hires Table'[Agency Name] )
VAR ReqTotal =
    COUNT ( 'Hires Table'[Req Number] ) + 0
RETURN
    CALCULATE ( ReqTotal, 'Agency Table'[Agency Name] = Agency )

This is the result