Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
@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
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.
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
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])
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
25 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
33 | |
27 | |
16 | |
14 | |
13 |