Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I need to count and group employee records, where the records to be counted are those with the maximum date for the unique employee ID relative to a date given; in addition, there may exist multiple records on a given date for the employee ID differentiated by sequence numbers, and in that case I need to count only the record with the greatest sequence number, for that employee and date.
In SQL I would do this:
select count(*), e_class
from mytable m
where
and m.eff_date = (select max(m2.eff_date) from mytable m2 where m.ee_id = m2.ee_id
and m2.eff_date < sysdate)
and m.eff_seq = (select max(m3.eff_seq) from mytable m3 where m3.ee_id = m.ee_id
and m3.eff_seq = m.eff_date)
group by e_class
;
Sample data:
EE_ID | eff_date | eff_seq | e_class |
123 | 01_JAN_2020 | 0 | A |
456 | 03_JAN_2020 | 0 | A |
123 | 10_JAN_2020 | 0 | A |
123 | 10_JAN_2020 | 1 | T |
Expected output:
When run as of Jan 2 2020
- 1 row with e_class=A
When run as of Jan 4 2020
- 2 rows with e_class=A
When run as of Jan 30 2020
- 1 row with e_class=A, which is ee_id 456
- 1 row with e_class=T, which is ee_id 123, because the greatest eff_date prior and eff_seq prior to the run date for this ee_id is the row with eff_date 10_JAN_2020 and eff_seq = =1.
Can anyone provide suggestions how to do this in DAX?
Thanks
Solved! Go to Solution.
Hi @cv77 ,
Based on your description, you can create this measure, put it in the visual filter and set its value as 1:
Visual control =
VAR _count =
IF (
SELECTEDVALUE ( 'Table'[eff_date] ) <= SELECTEDVALUE ( 'Date'[Date] ),
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[EE_ID]
IN DISTINCT ( 'Table'[EE_ID] )
&& 'Table'[eff_seq] = MAX ( 'Table'[eff_seq] )
&& 'Table'[eff_date] <= SELECTEDVALUE ( 'Date'[Date] )
)
)
)
RETURN
IF ( _count <= 1 && _count > 0, 1, 0 )
Create this measure to count and put it in the card visual:
Count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[eff_date] <= SELECTEDVALUE ( 'Date'[Date] )
&& 'Table'[eff_seq]
= CALCULATE (
MAX ( 'Table'[eff_seq] ),
FILTER (
ALL ( 'Table' ),
'Table'[EE_ID] = EARLIER ( 'Table'[EE_ID] )
&& 'Table'[eff_date] <= SELECTEDVALUE ( 'Date'[Date] )
)
)
)
)
Attached a sample file in the below, hoeps to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! I appreciate the response. This works.
Hi @cv77 ,
Based on your description, you can create this measure, put it in the visual filter and set its value as 1:
Visual control =
VAR _count =
IF (
SELECTEDVALUE ( 'Table'[eff_date] ) <= SELECTEDVALUE ( 'Date'[Date] ),
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[EE_ID]
IN DISTINCT ( 'Table'[EE_ID] )
&& 'Table'[eff_seq] = MAX ( 'Table'[eff_seq] )
&& 'Table'[eff_date] <= SELECTEDVALUE ( 'Date'[Date] )
)
)
)
RETURN
IF ( _count <= 1 && _count > 0, 1, 0 )
Create this measure to count and put it in the card visual:
Count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[eff_date] <= SELECTEDVALUE ( 'Date'[Date] )
&& 'Table'[eff_seq]
= CALCULATE (
MAX ( 'Table'[eff_seq] ),
FILTER (
ALL ( 'Table' ),
'Table'[EE_ID] = EARLIER ( 'Table'[EE_ID] )
&& 'Table'[eff_date] <= SELECTEDVALUE ( 'Date'[Date] )
)
)
)
)
Attached a sample file in the below, hoeps to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@cv77 Probably get faster solution if you post sample data and expected output versus SQL.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
85 | |
65 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |