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

[NS05112021]

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 ID	Date	LOB	Shift	Additional Shift
1	6/4/2024	Guard	MID
1	6/4/2024	Guard	MID	NIGHT
2	6/4/2024	Guard	NIGHT
2	6/4/2024	Guard	NIGHT
3	6/4/2024	HKL	DAY
3	6/4/2024	HKL	DAY
4	6/4/2024	HKL	MID
4	6/4/2024	HKL	MID
5	6/4/2024	Guard	MID
5	6/4/2024	Guard	MID
6	6/4/2024	HKL	NIGHT
6	6/4/2024	HKL	NIGHT
7	6/4/2024	Guard	NIGHT	DAY
7	6/4/2024	Guard	NIGHT
8	6/4/2024	Guard	DAY
8	6/4/2024	Guard	DAY
9	6/4/2024	Guard	DAY
9	6/4/2024	Guard	DAY

Here is the expected outcome I am looking for: 

Guards Night Shift  = 3

[Jihwan_Kim]

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]

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

	GUARDS	HKL
NIGHT	3	1
DAY	3	1
MID	2	1

[v-yiruan-msft]

@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

[NS05112021]

Thank you. Now I need to add dates into this somehow. If _shift = Night then take selected date from my slicer and subtract 1 day. because I want to pull in the night before to be included in todays report. so any night shifts from 6/3 would count when 6/4 was selected. 

Also-
If 'Table'[Shift]= day then take selected date from my slicer. But if 'Table'[Additional Shift] = day then take the selected day from the slicer and subtract one day. 

If _shift =mid, use the selected day from the slicer for all. 

[NS05112021]

@v-yiruan-msft 

[v-yiruan-msft · Accepted solution]

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

[NS05112021]

Thank you so much. I think I only have one more question. I want to create an identical matrix but instead of distinct count of employee ID, I want to sum to columns. Columns arenamed Table[Punch hours] and Table[Additional Hours]. 

I think I need the exact same formula only changing this part

DISTINCTCOUNT(Merge1[Employee Name.1.2.1])

[NS05112021]

@v-yiruan-msft 
Actually I think I need this logic on the new matrix. 

If 'Table'[Shift]= Night then take selected date from my slicer and subtract 1 day and  SUM(Table[Punch hours].

If 'Table'[Additional Shift] = Night then take selected date from my slicer and subtract 1 day and  SUM(Table[Additional hours].

If 'Table'[Shift]= day then take selected date from my slicer and SUM(Table[Punch hours] .

If 'Table'[Additional Shift] = day then take the selected day from the slicer and subtract one day and SUM(Table[Additional hours]. 

If _shift =mid, use the selected day from the slicer for all and SUM(Table[Punch hours] +  SUM(Table[Additional hours].

Still looking to have shift and lob in the view like this but replacing headcounts with hours.