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

Question

I have a Fact Table with golfer scores.&nbsp; 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.&nbsp;Example:Play 1 has 63 records in the Fact Table18 records for 18 holes played in round 19 records for 9 holes played in round 218 records for 18 holes played in round 39 records for 9 holes played in round 49 records played for 9 holes in round 5PLayer 2 only played rounds 1,2 &amp;4.&nbsp; He has 36 records in the fact table&nbsp;For each Round_ID in the ROUNDS table the ROUND_CNT = number of holes played in the round divided by 18.&nbsp; Is usually either .5 or 1 but can sometimes be different such as .3333&nbsp;The Rounds Dimension table looks like below.&nbsp;&nbsp;ROUND_IDROUND_CNT112.5314.55.5&nbsp;&nbsp;Player 1 total round count unfiltered = 3.5 (played all 5 rounds: 1 + .5 + 1 + .5 +.5)Player 2 total round count unfiltered = 2&nbsp;(played rounds 1,2 &amp;4: 1 + .5 + .5)&nbsp;In the visualization if I selectedPlayer 1 on the player slicer and Rounds 1 &amp; 2 on the Rounds slicer: Calculated measure should = 1.5Players 1 &amp; 2 on the player slicer and Rounds 1 &amp; 2 on the Rounds slicer: Calculated measure should = 3Players 1 &amp; 2 on the player slicer and Rounds 1 &amp; 2 &amp; 3 on the Rounds slicer: Calculated measure should = 4&nbsp;&nbsp;The portion of the data model relevant looks like this:&nbsp;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,&nbsp; but I want to learn the pattern to make this calculation this in DAX without a work around.&nbsp;&nbsp;&nbsp;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.&nbsp; Then build the calculated measure from this virtual table.&nbsp; So far I've come up with the DAXX below but that is not quite there.&nbsp; I did try to start off using SELECTCOLUMNS as the basis but got stuck so I switched over to SUMMARIZE&nbsp;SUMMARIZE (&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROUNDS,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"[ROUND_CNT]", LOOKUPVALUE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROUNDS[ROUND_CNT],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROUNDS[ROUND_ID], HANDICAP_PERFORMANCE[ROUND_ID]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;HANDICAP_PERFORMANCE[ROUND_ID],&nbsp;&nbsp;&nbsp;&nbsp;HANDICAP_PERFORMANCE[PLAYER_ID])&nbsp;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.&nbsp;Thanks

v-alq-msft · Accepted Answer

Hi,&nbsp;Georgia_Mike&nbsp;
&nbsp;
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Handicap:

&nbsp;
Players:

&nbsp;
Rounds:

&nbsp;
Relationship:

&nbsp;
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]
)
&nbsp;
Result:

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

Forum Discussion

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

2 Replies

Related content

distinct count of multiple columns

Getting distinct values for multiple columns

Count Distinct Across Multiple Columns in PowerBI

Count distinct rows based on multiple columns

Distinctive Count across two columns