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.
Hello community,
My problematic is straight forward I need to add DAX column. I have cirteria x, y and z. I can identify duplicate with this formula:
Duplicates count =
var x= Criteria
return
COUNTROWS(FILTER(Table,criteria = x)) this give me the number of duplicates (grouping).
Then for every duplicates I need to put a 1 to the most recent date of the grouping and a 0 to the oldest.
Is that even possible to do in DAX add Column?
Thanks for your help,
Mark Lovejoy
Solved! Go to Solution.
Hi!
You should be able to do something like this:
Most recent date =
VAR __IsDuplicate = //Checks if row has a duplicate. If all rows has duplicates, this is not needed.
If('Table'[Duplicates]>0,TRUE(),FALSE())
VAR __MaxDate = //Finds the most recent (MAX()) date for current criteria
CALCULATE(
max('Table'[Date]),
FILTER(all('Table'),'Table'[Criteria] = EARLIER('Table'[Criteria])
)
)
return
if(__IsDuplicate, //If current row is a duplicate
if('Table'[Date] = __MaxDate, //And current row date is the most recent date for this criteria
1, //return 1
0 //else return 0
),
0 else return 0
)
Hope this helps!
Hi @mlovejoy
please try
Result =
MAXX (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Criteria] ) ),
'Table'[Date]
)
Hi!
You should be able to do something like this:
Most recent date =
VAR __IsDuplicate = //Checks if row has a duplicate. If all rows has duplicates, this is not needed.
If('Table'[Duplicates]>0,TRUE(),FALSE())
VAR __MaxDate = //Finds the most recent (MAX()) date for current criteria
CALCULATE(
max('Table'[Date]),
FILTER(all('Table'),'Table'[Criteria] = EARLIER('Table'[Criteria])
)
)
return
if(__IsDuplicate, //If current row is a duplicate
if('Table'[Date] = __MaxDate, //And current row date is the most recent date for this criteria
1, //return 1
0 //else return 0
),
0 else return 0
)
Hope this helps!
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |