Distinct Values of multiple columns from one table with a lookup column from another table

[Georgia_Mike]

I have a Fact Table with golfer scores.  1 row per hole played by a golfer. I want to create a calculated measure that will give me the count of the number of rounds played with an outer filter context from slicers.

Example:

Play 1 has 63 records in the Fact Table

• 18 records for 18 holes played in round 1
• 9 records for 9 holes played in round 2
• 18 records for 18 holes played in round 3
• 9 records for 9 holes played in round 4
• 9 records played for 9 holes in round 5

PLayer 2 only played rounds 1,2 &4.  He has 36 records in the fact table

For each Round_ID in the ROUNDS table the ROUND_CNT = number of holes played in the round divided by 18.  Is usually either .5 or 1 but can sometimes be different such as .3333

The Rounds Dimension table looks like below.  

ROUND_ID	ROUND_CNT
1	1
2	.5
3	1
4	.5
5	.5

Player 1 total round count unfiltered = 3.5 (played all 5 rounds: 1 + .5 + 1 + .5 +.5)

Player 2 total round count unfiltered = 2 (played rounds 1,2 &4: 1 + .5 + .5)

In the visualization if I selected

• Player 1 on the player slicer and Rounds 1 & 2 on the Rounds slicer: Calculated measure should = 1.5
• Players 1 & 2 on the player slicer and Rounds 1 & 2 on the Rounds slicer: Calculated measure should = 3
• Players 1 & 2 on the player slicer and Rounds 1 & 2 & 3 on the Rounds slicer: Calculated measure should = 4 

The portion of the data model relevant looks like this:

I can easily solve this in query editor bringing the ROUNT_CNT over into the fact table and summing distinct PLAYER_ID/ROUND_ID from there,  but I want to learn the pattern to make this calculation this in DAX without a work around.  

My approach is to get the distinct combinations of PLAYER_ID and ROUND_ID from the fact table and then lookup the ROUND_CNT from the ROUNDS dimension table.  Then build the calculated measure from this virtual table.  So far I've come up with the DAXX below but that is not quite there.  I did try to start off using SELECTCOLUMNS as the basis but got stuck so I switched over to SUMMARIZE

SUMMARIZE (
    ADDCOLUMNS (
        ROUNDS,
        "[ROUND_CNT]", LOOKUPVALUE (
            ROUNDS[ROUND_CNT],
            ROUNDS[ROUND_ID], HANDICAP_PERFORMANCE[ROUND_ID]
        )
    ),
    HANDICAP_PERFORMANCE[ROUND_ID],
    HANDICAP_PERFORMANCE[PLAYER_ID]
)

I am trying to break this into pieces so that I can see the virtual table in DAX STUDIO and then create the calculated measure against the virtual table.

Thanks

[Georgia_Mike]

Thank you good sir.  I was so close.   My issue with learning Dax is that unlike SQL you cant see the middle layers of your logic.  If I could see exactly what the functions are doing to the data it would be so much easier to debug faulty logic.  Thanks again

[v-alq-msft · Accepted solution]

Hi, @Georgia_Mike 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Handicap:

Players:

Rounds:

Relationship:

You may create a measure as below.

Result = 
SUMX(
    SUMMARIZE(
        Handicap,
        Handicap[Player_ID],
        Handicap[Round_ID],
        "CNT",
        LOOKUPVALUE(Rounds[Round_CNT],Rounds[Round_ID],Handicap[Round_ID])
    ),
    [CNT]
)

Result:

Best Regards
Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.