Forum Discussion

MickeLearnPBI's avatar
MickeLearnPBI
Icon for Helper I rankHelper I
2 years ago

Unique combination av values

I would like to see unique combination of number of people working 1 or more departments. For example Matt, he works on both Sales and Production, that combination has no other = result 1 staff. Woul...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi MickeLearnPBI 

 

First, we need to create a calculated table that consolidates the unique combinations of PersonID and Department.

UniqueCombinations = 
SUMMARIZE(
    'Table', 
    'Table'[PersonID], 
    'Table'[Department], 
    "Count", COUNTROWS('Table')
)

 

 

Next, we create a measure to count the unique combinations.

Count of Unique Combinations = 
CALCULATE(
    DISTINCTCOUNT('UniqueCombinations'[PersonID]), 
    FILTER(
        'UniqueCombinations', 
        'UniqueCombinations'[Count] > 1
    )
)

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

MickeLearnPBI's avatar
MickeLearnPBI
Icon for Helper I rankHelper I
2 years ago

I tried that but it didn't seem to work. Let's say the number for the measure is 0 from the start, but when I then select the text Sales and Production with CTRL, then the number should show 1.
And that's because it's only Matt who works on this combination that I marked in the report.

Hope you understand what I mean?