Unique combination av values

[MickeLearnPBI]

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. Would I also mark Consultat (+Sales and Production), then there is no staff with that combination.

Is it possible to create some DAX to get the desired result?

PersonIDFirstNameLastNameBusinessUnitDepartment

556677	Matt	Olsen	Sales	Department 1
556677	Matt	Olsen	Production	Department 1
775566	Jenni	Grant	Sales	Department 1
445566	Daniel	LaRusso	Consultant	Department 1
994455	Sarah	Connor	Sales	Department 1
994455	Sarah	Connor	Sales	Department 2
1122	John	Connor	Sales	Department 1

[Anonymous]

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]

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?