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 everybody,
I have an issue creating a column from what I feel could come from a "for each" loop. As no function of the sort exists in DAX I am stuck.
Here is a simplified version of my data
ID Status
554 1
555 1
555 3
556 2
The IDs are not unique so one ID can have several status (2 or more), I would like to create a column that find the maximum status value for each ID (ex : Identify that for ID 555 the max status is 3 and in the new column replace 1 by 3). That would give us :
ID Status Max status
554 1 1
555 1 3
555 3 3
556 2 2
Does somedy know how to achieve that?
Many thanks for your help!!
Best,
Léa
Solved! Go to Solution.
Hi @LéaGr ,
You can create a Calculated column as follows using DAX:
it's working for numbers perfectly but what about incase of text
e.g
name visitplace
x a
x b
x a
y b
Requied output:-
name visitplace MAX
x a a
x b a
x a a
y b b
Hi @LéaGr ,
You can create a Calculated column as follows using DAX:
Thanks for your swift answer @Pragati11 !!
That works, I will mark it as the solution. I have a complementary question thow, will the column adapt to the filters? I have another column with dates and a date filter in my page, will the maximum be determined between the selected date or once and for all among all data?
It it does not adapt to the filter, do you know how I could acheive that?
Many thanks,
Best,
Léa
Hi @LéaGr ,
Yes this solution will work with filters as well. You can test it.
If it doen't work let me know.
Thanks,
Pragati
Hi @Pragati11 ,
I tested recently the solution and I was indeed able to create the calculated column but it does not adapt to filter. Indeed I have a date filter and the maximum seems to be calculated among the whole table and not the filtered table by date.
Maybe an equivalent measure could do the job?
I can send you the pbix if it helps.
Thanks a lot!
Best,
Léa
Gear thank you very much! I really appreciate how helpful and fast you are.
Have a great day!
Léa
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |