Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mortenhaga
Frequent Visitor

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

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):

 

mortenhaga_0-1708950415829.png

 

MemberIDSourcePriorityApplicationDateRecordEffectiveDateIsCurrent
45412023-01-07T16:36:04.000+00:002023-01-10T00:00:00.000+00:00FALSE
45422023-01-07T16:36:04.000+00:002023-01-21T00:00:00.000+00:00FALSE
78912023-01-19T17:16:05.000+00:002023-01-21T00:00:00.000+00:00FALSE
78922023-01-19T17:16:05.000+00:002023-03-08T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-03-08T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-03-09T00:00:00.000+00:00FALSE
78922023-01-19T17:16:05.000+00:002023-03-09T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-03-10T00:00:00.000+00:00FALSE
78922023-01-19T17:16:05.000+00:002023-03-16T00:00:00.000+00:00FALSE
78922023-01-19T17:16:05.000+00:002023-07-19T00:00:00.000+00:00FALSE
78932023-01-19T17:16:05.000+00:002023-07-24T00:00:00.000+00:00TRUE
101112023-07-20T13:17:14.000+00:002023-07-24T00:00:00.000+00:00FALSE
45422023-01-07T16:36:04.000+00:002023-07-24T00:00:00.000+00:00FALSE
101122023-07-20T13:17:14.000+00:002023-08-02T00:00:00.000+00:00TRUE
45412023-01-07T16:36:04.000+00:002023-08-02T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-08-23T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-08-24T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-08-24T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-08-25T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-09-22T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002023-12-06T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002024-01-13T00:00:00.000+00:00FALSE
45412023-01-07T16:36:04.000+00:002024-01-31T00:00:00.000+00:00TRUE

 

Any help very much appreciated.

 

 

 

 

1 ACCEPTED SOLUTION

hi @mortenhaga 

 

Step1

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

 

talespin_0-1709133930854.png

 

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. 
talespin_1-1709134289354.png

 

 

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

View solution in original post

9 REPLIES 9
talespin
Solution Sage
Solution Sage

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.

101122023-07-20T13:17:14.000+00:002023-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])
 
talespin_0-1709032672787.png

 

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

hi @mortenhaga 

 

Step1

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

 

talespin_0-1709133930854.png

 

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. 
talespin_1-1709134289354.png

 

 

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

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! 🙂

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.

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

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
Community Champion
Community Champion

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.

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!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors