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 am tring to create a calculated column called 'IsMax' which will flag the rows from the max month per ID. There may be more than one row per ID that relates to the max month.
Below is a sample of what I am trying to achieve:
ID | Month | IsMax |
A | Jan-21 | 1 |
A | Jan-21 | 1 |
A | Dec-20 | |
A | Dec-20 | |
A | Dec-20 | |
B | Feb-21 | 1 |
B | Feb-21 | 1 |
B | Dec-20 | |
B | Dec-20 | |
C | Nov-20 | |
C | Dec-20 | 1 |
C | Dec-20 | 1 |
C | Dec-20 | 1 |
Any help is appreciated.
Solved! Go to Solution.
Hi gclements,
Could you check if this calculated column does the trick for you? For every Month record it will check whether the value is equal to the max month value of the associated ID.
MaxDatePerID =
IF (
TestTable[Month]
= CALCULATE (
MAX ( TestTable[Month] ),
FILTER ( TestTable, TestTable[ID] = EARLIER ( TestTable[ID] ) )
),
1,
0
)
Hope it helps!
Regards,
Tim
Proud to be a Super User!
Hi gclements,
Could you check if this calculated column does the trick for you? For every Month record it will check whether the value is equal to the max month value of the associated ID.
MaxDatePerID =
IF (
TestTable[Month]
= CALCULATE (
MAX ( TestTable[Month] ),
FILTER ( TestTable, TestTable[ID] = EARLIER ( TestTable[ID] ) )
),
1,
0
)
Hope it helps!
Regards,
Tim
Proud to be a Super User!
Thanks for the solution. I have now realised that I have asked the wrong question as this will not resolve what I am trying to do. Nevertheless this is the answer to my question so I will mark it as so and ask another question in another post.
Hi @gclements
What's the logic behind which month is the Max?
For A you have flagged Jan, but for C you have flagged Dec?
Regards
Phil
Proud to be a Super User!
It is to identify the maxium month value for each group. So for group A the maxium month is Jan-21, therefore all rows for Jan-21 are flagged, for group B it is Feb-21...etc.
Your initial post had Dec21 in row 3 but I see that has now been corrected. So the logic is clear now. having Jan21 as the max didn't make sense with Dec21 in there.
Phil
Proud to be a Super User!
Yes sorry, I noticed there was a slight mistake in one of the months.
Just to be a bit clearer, I am trying to calculate the 'IsMax' column.
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 |
---|---|
107 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |