Forum Discussion
Find row value based on two other columns
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!
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.
9 Replies
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".
- parry2k
Super User
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.
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 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.
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 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.
