Forum Discussion
How would I conditional format this in PBI for a table?
Hi,
I am working on a report that changes the background color of a date column based on how many days a deal has been in a stage. My team would like to set these conditional rules based on which stage it is in (see below). However, I am not sure how to do the write the rules to change based on which stage. For example, my report is working based on deal stage 6 rules but for all stages.
I am not sure were to add the deal stage parameter to create it as my sales directors intended.
Any help is appreciated.
Hi Anonymous ,
If you can get the deal stage in the current context, try this:
Measure = VAR __cur_stage = SELECTEDVALUE('Table'[Deal Stage]) VAR __days = SUM('Table'[Days Opened]) VAR __result = SWITCH( TRUE(), __cur_stage IN {5,6}, SWITCH( TRUE(), __days < 0 && __days >= -60, "Green", __days < -60 && __days >= -90, "Yellow", __days < -90 && __days >= -1000, "Red" ), __cur_stage = 4, SWITCH( TRUE(), __days < 0 && __days >= -30, "Green", __days < -30 && __days >= -60, "Yellow", __days < -60 && __days >= -1000, "Red" ), __cur_stage IN {2,3}, SWITCH( TRUE(), __days < 0 && __days >= -15, "Green", __days < -15 && __days >= -30, "Yellow", __days < -30 && __days >= -1000, "Red" ) ) RETURN __resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
7 Replies
Hi Anonymous ,
If you can get the deal stage in the current context, try this:
Measure = VAR __cur_stage = SELECTEDVALUE('Table'[Deal Stage]) VAR __days = SUM('Table'[Days Opened]) VAR __result = SWITCH( TRUE(), __cur_stage IN {5,6}, SWITCH( TRUE(), __days < 0 && __days >= -60, "Green", __days < -60 && __days >= -90, "Yellow", __days < -90 && __days >= -1000, "Red" ), __cur_stage = 4, SWITCH( TRUE(), __days < 0 && __days >= -30, "Green", __days < -30 && __days >= -60, "Yellow", __days < -60 && __days >= -1000, "Red" ), __cur_stage IN {2,3}, SWITCH( TRUE(), __days < 0 && __days >= -15, "Green", __days < -15 && __days >= -30, "Yellow", __days < -30 && __days >= -1000, "Red" ) ) RETURN __resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
- parry2k
Super User
Anonymous does the transaction table have deal stage column?
- parry2k
Anonymous then it should be super easy. It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.
- parry2k
Anonymous or check out this video on my channel - it addresses similar question: How to make conditional formatting scalable and easy to manage in Power BI (youtube.com)
- parry2k
Anonymous it has to be a measure not a column.
