Forum Discussion
Conditional lookup
Hello all, a bit on a confusing one but hopefully someone can help
i have two tables, i need table 1 to return green, orange or red based on the matching rule. e.g
workstream 4 should bring back Green. any ideas on the dax for this or if i need to shape the data diffferent. Workstreams are linked together.
NewbieJono you should transform the lookup table with these columns and then should be easy, basically each row will become 3 rows, one for green, one for orange, and another one for red.
Work Stream
Min Value
Max Value
Color Value
✨ 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 efforts 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.
NewbieJono see attached and tweak it as you see fit. I just changed the value in lookup table to see different colors:
✨ 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 efforts 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.
6 Replies
NewbieJono you should transform the lookup table with these columns and then should be easy, basically each row will become 3 rows, one for green, one for orange, and another one for red.
Work Stream
Min Value
Max Value
Color Value
✨ 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 efforts 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.
sorry how would i do it frm here, woudl this create a many to many relationship? my table 1 will have many duplicate workstreams
NewbieJono see attached and tweak it as you see fit. I just changed the value in lookup table to see different colors:
✨ 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 efforts 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.
NewbieJono well you will not create the relationship but use the following DAX code to get the respective color:
Color = VAR __DaysOld = Table1[Days Old] VAR __color = CALCULATE ( MAX ( 'LookupTable'[Color] ), __DaysOld >= 'LookupTable'[Min], __DaysOld <= 'LookupTable'[Max] )✨ 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 efforts 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.
sorry for being stupid bit how would this account for the different rules for different workstreams.
is there any way this could be done as a calculate coloumn
Thank you so much for your time on this. i think i can this up and running now.
