Get the latest record in an SCD2 for a unique ID, count unique IDS, within period set by date slicer

[mortenhaga]

Hi all

I have been struggling with this challenge latetly.

My goal is to create a measure that count unique IDs that has the "Priority" of 1, within a set date period from the date slicer. 

The data comes from an SCD2 table that records entries of all unique contracts ("Applicants"). The one column that changes frequently is the column named "Priority". My goal is to, within the set date period from the date slicer, find the latest entry for all unique contracts and then do a unique count for all with "Priority" of 1. "Priority" can be filtered on the report page as well so I guess that filtering doesnt need to be done in DAX. The measure uses USERELATIONSHIP with "ApplicationDate".

In plains SQL this can be solved with a ROW_NUM() with descending order, and then filter on the row number. I havent managed to use ROWNUMBER() in DAX in a dynamic way, having the date slicer dictating the ROWNUMBER() output and passing it forward in the measure.

One way I could think of solving this is to lock in the count for each locked time period (say per week), but the users would like to see if it works with the date slicer.

Sample from my table (Sorted by RecordEffectiveDate):

MemberIDSource	Priority	ApplicationDate	RecordEffectiveDate	IsCurrent
454	1	2023-01-07T16:36:04.000+00:00	2023-01-10T00:00:00.000+00:00	FALSE
454	2	2023-01-07T16:36:04.000+00:00	2023-01-21T00:00:00.000+00:00	FALSE
789	1	2023-01-19T17:16:05.000+00:00	2023-01-21T00:00:00.000+00:00	FALSE
789	2	2023-01-19T17:16:05.000+00:00	2023-03-08T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-03-08T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-03-09T00:00:00.000+00:00	FALSE
789	2	2023-01-19T17:16:05.000+00:00	2023-03-09T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-03-10T00:00:00.000+00:00	FALSE
789	2	2023-01-19T17:16:05.000+00:00	2023-03-16T00:00:00.000+00:00	FALSE
789	2	2023-01-19T17:16:05.000+00:00	2023-07-19T00:00:00.000+00:00	FALSE
789	3	2023-01-19T17:16:05.000+00:00	2023-07-24T00:00:00.000+00:00	TRUE
1011	1	2023-07-20T13:17:14.000+00:00	2023-07-24T00:00:00.000+00:00	FALSE
454	2	2023-01-07T16:36:04.000+00:00	2023-07-24T00:00:00.000+00:00	FALSE
1011	2	2023-07-20T13:17:14.000+00:00	2023-08-02T00:00:00.000+00:00	TRUE
454	1	2023-01-07T16:36:04.000+00:00	2023-08-02T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-08-23T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-08-24T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-08-24T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-08-25T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-09-22T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2023-12-06T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2024-01-13T00:00:00.000+00:00	FALSE
454	1	2023-01-07T16:36:04.000+00:00	2024-01-31T00:00:00.000+00:00	TRUE

Any help very much appreciated.

[talespin]

hi @mortenhaga 

I made one assumption, please let me know if change is required. Based on date slicer first I am getting records with latest Effective date and then filtering on Priority = 1. If you want priority to be checked first and then fetch latest date, then need to make change. I am using single table.

For testing I changed the priority to 1.

1011

2

2023-07-20T13:17:14.000+00:00

2023-08-02T00:00:00.000+00:00

TRUE

UniqueCount =

VAR _SummTbl = ADDCOLUMNS(

                            SCD2,                            

                            "@MaxDat",

                            VAR _MemberID = [MemberIDSource]

                            RETURN CALCULATE(MAX(SCD2[RecordEffectiveDate]), REMOVEFILTERS(SCD2), VALUES(SCD2[RecordEffectiveDate]), SCD2[MemberIDSource] = _MemberID )

                        )

VAR _FltrTbl = FILTER(_SummTbl, [RecordEffectiveDate] = [@MaxDat] && [Priority] = 1)

RETURN COUNTX( _FltrTbl, [MemberIDSource])

[mortenhaga]

@talespin 

I figured this works for the current date period selected by the date slicer, so thats very good! But if you want to use the exact measure to show LYTD (Last year to date), ie, calculate distinct count for the same period the date slicer selects, only last year, how would you incorporate that filter in your DAX? I also want to filter on a column named "SchoolYear". Do you set that filter before or after you do the MAX? Can the same operation be used with ROWNUMBER?

[talespin · Accepted solution]

hi @mortenhaga 

Step1

You will need to use Date table, One to many(Single)

Step2

For date table I have used this. You will need to expand it to your requirement and it should have all the dates within date range ( including SPYL dates ).

CALENDAR =

VAR _Dt = MIN(SCD2[RecordEffectiveDate])

VAR _MinDate = DATE( YEAR(_Dt), 1,1)

VAR _MaxDate = MAX(SCD2[RecordEffectiveDate])

RETURN CALENDAR(_MinDate, _MaxDate)

Step3

Measure for Unique count

UniqueCount =

VAR _MinDt = MIN('CALENDAR'[Date])

VAR _MaxDt = MAX('CALENDAR'[Date])

VAR _SummTbl = ADDCOLUMNS(

                            SCD2,                            

                            "@MaxDate",

                            VAR _MemberID = [MemberIDSource]

                            RETURN CALCULATE(MAX(SCD2[RecordEffectiveDate]), REMOVEFILTERS(SCD2), REMOVEFILTERS('CALENDAR'), 'CALENDAR'[Date] >= _MinDt && 'CALENDAR'[Date] <= _MaxDt, SCD2[MemberIDSource] = _MemberID )

                        )

VAR _FltrTbl = FILTER(_SummTbl, [RecordEffectiveDate] = [@MaxDate] && [Priority] = 1)

RETURN COUNTX(_FltrTbl, [MemberIDSource])

Measure for SPYL

UniqueCount SPLY =

VAR _MinDt = MIN('CALENDAR'[Date])

VAR _MinDtLY = DATE( YEAR(_MinDt)-1, MONTH(_MinDt), DAY(_MinDt))

VAR _MaxDt = MAX('CALENDAR'[Date])

VAR _MaxDtLY = DATE( YEAR(_MaxDt)-1, MONTH(_MaxDt), DAY(_MaxDt))

VAR _SummTbl = ADDCOLUMNS(

                            ALL(SCD2[MemberIDSource],SCD2[Priority],SCD2[RecordEffectiveDate]),

                            "@MaxDate",

                            VAR _MemberID = [MemberIDSource]

                            RETURN CALCULATE(MAX(SCD2[RecordEffectiveDate]), REMOVEFILTERS(SCD2), SCD2[MemberIDSource] = _MemberID, REMOVEFILTERS('CALENDAR'), 'CALENDAR'[Date] >= _MinDtLY && 'CALENDAR'[Date] <= _MaxDtLY )

                        )

VAR _FltrTbl = FILTER(_SummTbl, [RecordEffectiveDate] = [@MaxDate] && [Priority] = 1)

RETURN COUNTX(_FltrTbl, [MemberIDSource])

Date in the right table, date slicer using date from Calendar table. First Card is Unique count and second card is Unique count SPLY. 

Regarding second question

I also want to filter on a column named "SchoolYear". Do you set that filter before or after you do the MAX?  - That depends, where you want to filter first and then take max date or you want fetch maxt date first and then apply filter.

If you want to fetch Max date for each member ID first and then apply your filter, put it here

VAR _FltrTbl = FILTER(_SummTbl, [RecordEffectiveDate] = [@MaxDate] && [Priority] = 1 && your filter here assuming its part of same table).

[mortenhaga]

I have given this a try, and as of now, I will accept it as solution because it gives me the number I want. However, its difficult to implement this with bringing in other slicers from different dims in my model, since the use of ADDCOLUMNS explicitly works with the table referenced and COUNTX only works with tables. If one could do this with using CALCULATE with a measure already created to count distincts that would be great. Other than that, Im very happy for your help and I have learned about ADDCOLUMNS and also REMOVEFILTERS (didnt know anything about that;)). Thanks! 🙂

[talespin]

hi @mortenhaga 

Please note that I am using REMOVEFILTERS(SCD2), if the slicer is on this table, it will remove that. I have included this to get rid of any filter coming from Table visual.

[mortenhaga]

Thanks for your answer. I will test this asap and get back to you 🙂

[mortenhaga]

Hi!

Yes this looks like a possible solution. Trying it out as we speak and will come back to you if it fits my needs.

Thank you 🙂

[123abc]

To accomplish your goal in Power BI using DAX, you can follow these steps:

1. Create a Calculated Column for Rank: First, you need to create a calculated column that ranks the entries for each unique contract based on the RecordEffectiveDate in descending order. This can be done using the RANKX function.

2. Filter by Date Slicer: You will then filter the data based on the date slicer to get the entries within the specified period.

3. Count Unique IDs with Priority 1: Finally, you'll count the unique IDs that have a priority of 1 within the filtered dataset.

Here's how you can write the DAX measure:

CountUniquePriority1 =
VAR MaxRankPerID =
CALCULATE (
MAX ( 'YourTable'[Rank] ),
ALLSELECTED( 'YourTable' ),
'YourTable'[Priority] = 1
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'YourTable'[UniqueID] ),
'YourTable'[Rank] = MaxRankPerID
)

Explanation:

• MaxRankPerID: Calculates the maximum rank per unique ID where the priority is 1, and considers the filters from the date slicer.
• DISTINCTCOUNT('YourTable'[UniqueID]): Counts the distinct number of Unique IDs where the rank matches the maximum rank calculated for each ID.

Replace 'YourTable', 'Rank', 'UniqueID', 'Priority', and 'RecordEffectiveDate' with your actual table and column names.

Ensure you have a properly defined relationship between the date slicer table and the date column in your SCD2 table.

This DAX measure should dynamically calculate the latest record for each unique contract within the specified date period and count the number of unique contracts with a priority of 1.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[mortenhaga]

Hi @123abc !

Thanks for taking your time to help.

This would be the ultimate solution to the challenge, but since calculated columns only refresh data once (when the report is opened or data is refreshed), this would fail to dynamically get the latest record based on the time period set by the date slicer. This is because when the user changes the slicer, the data would not change, since the rank from the calculated column is only calculated once. 

I initially looked at this as a possible solution: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Return-an-array-of-unique-values-in-...

But I havent managed to do it yet. Maybe you can have a look at the post?

Thanks!