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
I have a table with lots of property references that apear multiple times but with different dates. I would like to create a calculated column that returns 1 for the max date relating to each of these references els return 0
Ref | Date | DAX - Max Date |
132332 | 16/06/2011 | 0 |
132332 | 18/01/2014 | 0 |
132332 | 10/11/2015 | 0 |
132332 | 24/04/2017 | 0 |
132332 | 03/04/2020 | 0 |
132332 | 11/05/2020 | 1 |
90000 | 16/06/2011 | 0 |
90000 | 18/01/2014 | 0 |
90000 | 10/11/2015 | 0 |
90000 | 24/04/2017 | 0 |
90000 | 03/04/2020 | 0 |
90000 | 11/05/2021 | 1 |
thank you
RIchard
Solved! Go to Solution.
Hey @cottrera ,
try the following calculated column. I added some explanation in the code:
MaxDate =
-- Calculate the MAX date for the current REF-ID
VAR vMaxDate = CALCULATE ( MAX ( myTable[Date] ), ALLEXCEPT ( myTable, myTable[Ref] ) )
RETURN
-- When the date of the current row is equal to the vMaxDate then return 1 otherwise 0
IF (
myTable[Date] = vMaxDate,
1,
0
)
Appologies Denis my data model was Direct Query. When I changed it to Import your measure worked fine 😁
Hi Denis thank you for your quick reponse. I am getting these errors when adding the code to a column measure
Richard
Hey @cottrera ,
try the following calculated column. I added some explanation in the code:
MaxDate =
-- Calculate the MAX date for the current REF-ID
VAR vMaxDate = CALCULATE ( MAX ( myTable[Date] ), ALLEXCEPT ( myTable, myTable[Ref] ) )
RETURN
-- When the date of the current row is equal to the vMaxDate then return 1 otherwise 0
IF (
myTable[Date] = vMaxDate,
1,
0
)
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 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |