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): 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.
SQL query to DAX Measure conversion
I have the following SQL query that I have converted to DAX Measure SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY EOMDate DESC) FROM [TransArchive].dbo.AGGR_Transaction_ChannelUtilization WHERE EOMDate BETWEEN @dteEOMBegin AND @dteEOMEnd DAX Measure ::: Index = CALCULATE(COUNTROWS('Member Channel Utilization'), FILTER(ALLSELECTED ('Member Channel Utilization') ,FILTER('Member Channel Utilization','Member Channel Utilization'[Report Date]<= EARLIER('Member Channel Utilization'[Report Date])) ,FILTER('Member Channel Utilization','Member Channel Utilization'[EDWCustomerID]= EARLIER('Member Channel Utilization'[EDWCustomerID])))) Please Note :::: ReportDate = EOMdate and SSN =EDWCustomerID I have used ALLSELECTED becasue I want the rownum value to change based on the date range filter and /or any other filter selected by the user on the PowerBI report. When I run the query I get the following message "Too Many Arguments were passed to the FILTER Function. The Maximum argument count for the function is 2".... Could you please let me know how to make this DAX work . Thanks in advance
