Forum Discussion

LéaGr's avatar
LéaGr
Helper I
6 years ago
Solved

Create a column from loop

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

Need Help
  • Pragati11's avatar
    Pragati11
    6 years ago

    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

6 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    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's avatar
      LéaGr
      Helper I
      6 years ago

      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's avatar
        Pragati11
        Super User
        6 years ago

        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

  • BIswajit_Das's avatar
    BIswajit_Das
    Impactful Individual
    3 years ago

    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