Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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 ).
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).
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
|
@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)
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 ).
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 🙂
To accomplish your goal in Power BI using DAX, you can follow these steps:
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.
Filter by Date Slicer: You will then filter the data based on the date slicer to get the entries within the specified period.
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:
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
18 | |
14 | |
11 |
User | Count |
---|---|
44 | |
35 | |
25 | |
22 | |
22 |