Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I am struggling to pick up the first value registered for a id based on the earliest date.
So for the customer below, a membership is first created on 01.01.2016 and the first activity on this membership is created on 01.02.2017. For this membership I only want the first activity created to show up instead of all the others below. Then for the second membership which is created on 13.02.2019 the first activity is on 15.02.2019. I want to filter the table to only show the first activity for this membership instead of all.
I basically want to pick the acitivity row with the date closest to the membership created date for each customer id.
How can I achieve this in dax?
Solved! Go to Solution.
Hi @bininja
Create measures
Measure = DATEDIFF(MAX('Table'[created date]),MAX('Table'[activity date]),DAY) Measure 2 = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[id] = MAX ( 'Table'[id] ) && 'Table'[created date] = MAX ( 'Table'[created date] ) ), [Measure], , ASC, DENSE )
Hi @bininja
Create measures
Measure = DATEDIFF(MAX('Table'[created date]),MAX('Table'[activity date]),DAY) Measure 2 = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[id] = MAX ( 'Table'[id] ) && 'Table'[created date] = MAX ( 'Table'[created date] ) ), [Measure], , ASC, DENSE )
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur can this be done with a measure aswell? I see that you created a calculated column. I only have the possibility to create measures in the dataset as it is a live connection which limits alot of the options for me.
Hi,
I am not sure of how to solve this question with a measure.
Latest Date = CALCULATE ( MAX ( Table[Date] ), ALLEXCEPT ( Table, Table[ID] ) )Cheers!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |