topic Unique combination av values in DAX Commands and Tips

Unique combination av values

MickeLearnPBI — Tue, 28 May 2024 07:59:27 GMT

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

Re: Unique combination av values

Anonymous — Wed, 29 May 2024 02:45:04 GMT

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.

Re: Unique combination av values

MickeLearnPBI — Thu, 30 May 2024 12:05:09 GMT

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?