Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |