Forum Discussion

MickeLearnPBI's avatar
MickeLearnPBI
Helper 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. 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

556677MattOlsenSalesDepartment 1
556677MattOlsenProductionDepartment 1
775566JenniGrantSalesDepartment 1
445566DanielLaRussoConsultantDepartment 1
994455SarahConnorSalesDepartment 1
994455SarahConnorSalesDepartment 2
1122JohnConnorSalesDepartment 1

2 Replies

  • 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
      Helper 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?