Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Third column based on combination values two other columns

Hi!

 

I have a problem with creating a measure/column that gives a result based on the relation of two other columns.


This is my data

 

ID

Priority

1

High

1

Medium

2

Low

3

Medium

4

High

4

Low

5

Medium

5

Low

5

High

 

 

I want to create a measure/column in which the ID just gets one priority. If the ID has 2 or more priorities, I want the ID to be related to the highest priority. The data should therefore look like this:

 

ID

Priority

1

High

2

Low

3

Medium

4

High

5

High

 

Hope anyone can help 🙂

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    @Janou. High, Low and Medium going not just a max/min so create a column like

    range range ?

    Switch ([Priority],

    "High" ,3,

    "Medium" ,2,

    "low" ,1)

    select this measure with Id on any

    lastnonblankvalue(Table[Rank],max(Table[Priority]))

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    @Janou. High, Low and Medium going not just a max/min so create a column like

    range range ?

    Switch ([Priority],

    "High" ,3,

    "Medium" ,2,

    "low" ,1)

    select this measure with Id on any

    lastnonblankvalue(Table[Rank],max(Table[Priority]))

  • FarhanAhmed's avatar
    FarhanAhmed
    Icon for Community Champion rankCommunity Champion
    6 years ago

    A bit dirty workaround 

     

    Create  a column  

    Priority Column = SWITCH('Table'[Priority],
"High","1000",
"Medium","0500",
"Low","0000")&'Table'[Priority]

     

    Create a Measure 

    _Priority = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CALCULATE(MAX('Table'[Priority Column])),"1000",""),"0500",""),"0000","")

     

     

     

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Result = 
var tab = 
ADDCOLUMNS(
    ALL('Table'),
    "Flag",
    SWITCH(
        [Priority],
        "Low",1,
        "Medium",2,
        "High",3
    )
)
return 
    MAXX(
        FILTER(
            tab,
            [ID]=SELECTEDVALUE('Table'[ID])&&
            [Flag]=
            MAXX(
                FILTER(
                    tab,
                    [ID]=SELECTEDVALUE('Table'[ID])
                ),
                [Flag]
            )
        ),
        [Priority]
    )

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi All,

       

      Thanks for your help. While I was looking at your solutions I realized that I might not need a column but a measure. I want the data to be dynamic so that if I put a filter on it, the data changes accordingly. Is there anyone who can help me with that issue?

       

      Thanks in advance!