Forum Discussion

NS05112021's avatar
NS05112021
Frequent Visitor
2 years ago
Solved

How do I sum two distinct counts based on two filtered columns in DAX?

I am new to Power BI. I am looking to count the distinct Employee ID's based on 2 columns in the report view. 

 

In this example below, I am trying to count all distinct Employee IDs if LOB = 'Guard' and Shift='NIGHT' PLUS count all distinct Employee IDs if LOB = 'Guard' and Additional Shift='NIGHT'

 

Here is example data: 

Employee IDDateLOBShiftAdditional Shift
16/4/2024GuardMID 
16/4/2024GuardMIDNIGHT
26/4/2024GuardNIGHT 
26/4/2024GuardNIGHT 
36/4/2024HKLDAY 
36/4/2024HKLDAY 
46/4/2024HKLMID 
46/4/2024HKLMID 
56/4/2024GuardMID 
56/4/2024GuardMID 
66/4/2024HKLNIGHT 
66/4/2024HKLNIGHT 
76/4/2024GuardNIGHTDAY
76/4/2024GuardNIGHT 
86/4/2024GuardDAY 
86/4/2024GuardDAY 
96/4/2024GuardDAY 
96/4/2024GuardDAY 

 

Here is the expected outcome I am looking for: 

Guards Night Shift  = 3

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi NS05112021 ,

    You can update the formula of measure [Measure] as below, please find the details in the attachment.

    Measure = 
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _shift =
    SELECTEDVALUE ( 'Table'[Shift] )
VAR _lob =
    SELECTEDVALUE ( 'Table'[LOB] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Employee ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[LOB] = _lob
                && (
                    'Table'[Date]
                        = IF (
                            'Table'[Shift] = "NIGHT"
                                || 'Table'[Additional Shift] IN { "DAY", "NIGHT" },
                            _date - 1,
                            _date
                        )
                        && ( 'Table'[Shift] = _shift
                        || 'Table'[Additional Shift] = _shift )
                )
        )
    )

    Best Regards

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    expected result measure: =
VAR _list =
    SUMMARIZE (
        FILTER (
            Data,
            Data[LOB] = "Guard"
                && OR ( Data[Shift] = "NIGHT", Data[Additional Shift] = "NIGHT" )
        ),
        Data[Employee ID]
    )
RETURN
    COUNTROWS ( _list )
    • NS05112021's avatar
      NS05112021
      Frequent Visitor
      2 years ago

      Thank you. what if I wanted to creaet a matrix or a table to summarize with the expected outcome like this. 

       

       GUARDSHKL
      NIGHT31
      DAY31
      MID21
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Jihwan_Kim Thanks for your contribution on this thread.

        Hi NS05112021 ,

        You can follow the steps below to get it, please find the details in the attachment.

        1. Create two measures as below

        Measure = 
VAR _shift =
    SELECTEDVALUE ( 'Table'[Shift] )
VAR _lob =
    SELECTEDVALUE ( 'Table'[LOB] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Employee ID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[LOB] = _lob
                && ( 'Table'[Shift] = _shift
                || 'Table'[Additional Shift] = _shift )
        )
    )
        Count of employees = SUMX(VALUES('Table'[LOB]),[Measure])

        2. Create a matrix visual

        Best Regards