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.
I have a table that follows this structure
ID_Class | Date of conclusion |
1111-Class A | 13/04/2022 |
1111-Class B | 20/04/2022 |
2222-Class A | 26/07/2022 |
2222-Class A | 30/09/2022 |
2222-Class B | 14/02/2022 |
3333-Class A | 20/03/2022 |
The ID_Class column combines the person's unique id and the name of the class they attended. But sometimes there are people who do the same class twice or more but on different dates.
I wanted to create a column of TRUE and FALSE that would bring me the TRUE value whenever each distinct ID_Class is the most recent (since I want to fetch only the most recent courses taken by each person. If a person took the same course twice, I want to see only the most recent of these two courses).
The expected result would be something like this:
ID_Class | Date of conclusion | Most_Recent |
1111-Class A | 13/04/2022 | TRUE |
1111-Class B | 20/04/2022 | TRUE |
2222-Class A | 26/07/2022 | FALSE |
2222-Class A | 30/09/2022 | TRUE |
2222-Class B | 14/02/2022 | TRUE |
3333-Class A | 20/03/2022 | TRUE |
3333-Class A | 22/01/2022 | FALSE |
How can I do this?
Solved! Go to Solution.
@req77 you need a measure like following
Measure =
VAR dt =
MAX ( 'fact'[dt] )
VAR mxDt =
CALCULATE ( MAX ( 'fact'[dt] ), ALLEXCEPT ( 'fact', 'fact'[ID_Class] ) )
RETURN
SWITCH ( TRUE (), dt = mxDt, TRUE (), FALSE () )
@Sahir_Maharaj DAX rule of thumb, don't create calculated columns if you can create a measure
@req77 you need a measure like following
Measure =
VAR dt =
MAX ( 'fact'[dt] )
VAR mxDt =
CALCULATE ( MAX ( 'fact'[dt] ), ALLEXCEPT ( 'fact', 'fact'[ID_Class] ) )
RETURN
SWITCH ( TRUE (), dt = mxDt, TRUE (), FALSE () )
@Sahir_Maharaj DAX rule of thumb, don't create calculated columns if you can create a measure
Hi @req77,
Yes, you're correct. The [Date of conclusion] column should be a column in your table, not a measure. In a DAX formula for a calculated column, you can reference other columns in the same table, but you can't reference measures.
So, in this case, you need to make sure that the [Date of conclusion] column is a column in your table and not a measure. Then, you can reference it in your DAX formula to compare the date of conclusion for the current row with the date of conclusion for other rows with the same ID_Class.
Let me know if this works.
This formula uses a calculated column that creates a variable CurrentIDClass that stores the value of the ID_Class column for the current row. Then, it returns TRUE if the date of conclusion for the current row is equal to the minimum date of conclusion for all rows with the same ID_Class. Otherwise, it returns FALSE.
You can use the following DAX formula to achieve the desired result:
Most_Recent = VAR CurrentIDClass = MIN('Table'[ID_Class]) RETURN IF(CurrentIDClass = MIN(FILTER('Table', 'Table'[ID_Class] = MIN('Table'[ID_Class]))[Date of conclusion]), TRUE, FALSE)
Hi, @Sahir_Maharaj Thanks for the quick response! 🙂
I didn't quite understand the
[Date of conclusion]
part in your code. Does it use a measure for the date of conclusion? Because when I try to do it here, I can't pull the "Date of conclusion" column of my table, it only lets me bring measures.
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 |
---|---|
110 | |
96 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |