Create a column from loop

[LéaGr]

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

[BIswajit_Das]

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

[Pragati11 · Accepted solution]

Hi @LéaGr ,

You can create a Calculated column as follows using DAX:

TestCol = CALCULATE(

                                    MAX(Table1[Status]),

                                    FILTER(

                                               ALLSELECTED(Table1), Table1[ID] = EARLIER(Table1[ID])

                                              )

                                    )

Replace Table1 in above DAX expression with your's table-name.

Thanks,

Pragati

[LéaGr]

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

[Pragati11]

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

[LéaGr]

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

[LéaGr]

Gear thank you very much! I really appreciate how helpful and fast you are.

Have a great day!

Léa