Find row value based on two other columns

[prof_hoo]

I want to find the type based on a numeric column for each state. Here is an brief example of the data I have. I have a location column (State), a category column (Type), and a value column. For each state, I want the type with the highest value for all of the types for that state. E.g., type Z in Alabama has the highest value (4), so I want to display Z. Overall, however, the type with the highest sum is Y, so if my slicer  is set to "all" I want Y.

State	Type	Value
Alabama	W	1
Alabama	X	2
Alabama	Y	3
Alabama	Z	4
California	W	5
California	X	3
California	Y	4
California	Z	1
Vermont	X	1
Vermont	Y	1
Vermont	Z	2

For Alabama, my card should display "Z", for California , "W", for Vermont, "Z", and for all "Y".

I think I could do this by creating new tables, but I'm new to PBI and I'm trying to avoid that crutch. 

Thanks so much!

[parry2k · Accepted solution]

@prof_hoo Try this measure, change table and column name as per your model

Top Type = 
CALCULATE ( 
    MAX ( HighType[Type] ),
    TOPN (
        1, 
        ALLSELECTED ( HighType[Type] ),
        CALCULATE ( SUM ( HighType[Value] ) ),
        DESC
    )
)

Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k]

@prof_hoo now it makes a bit of sense, but why 'Y' for all, what is the logic behind that, or if more than one state is selected it will be 'Y'

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[prof_hoo]

Because if you sum up all 4 types, W = 6, X = 6, Y = 8, and Z = 7, so overall, Y is the the type with the highest value.

[parry2k]

@prof_hoo you can simply add a column in your table using following DAX expression and then use it in the visuals:

New Type = 
VAR __state = Table[State]
RETURN
SWITCH ( __state, 
   "California", "W",
   "Vermont", "Z",
   "Alabama", "Z",
   "Y"
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[prof_hoo]

This isn't the real data; the actual data is much larger and is constantly changing, so hard-coding a solution doesn't work. Thanks though!

[parry2k]

@prof_hoo I don't understand what do you mean by hardcoding, you provided the logic and the solution is based on your ask.

You need to provide more details to explain the logic so that community can provide you the help. 

[prof_hoo]

Apologies; I edited my post to better explain the logic I'm looking to create.

[davehus]

Hi @prof_hoo , Can you provide an example of the desired result?

Thanks.

Did I help you today? Please accept my solution and hit the Kudos button.

[prof_hoo]

There is a card which should show the appropriate type depending on the selection in the slicer. 

For Alabama, my card should display "Z"; for California , "W"; for Vermont, "Z"; and for all "Y".