SQL to DAX - displaying max value grouping by columns

[cv77]

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 

[cv77]

Thank you!  I appreciate the response.  This works.

[v-yingjl · Accepted 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.

[Greg_Deckler]

@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.