Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need: Dynamic Column/Measure that conditionally formats VTF% based on VTF% values and Month

Hi, I have a table that includes a column for VTF%, and an traffic light icon to the right of that VTF% value.  The traffic icon color depends on the VTF% and the month of the year. Below are the p...
Need Help
amitchandak's avatar
amitchandak
Super User
2 years ago

Anonymous , Try a measure like

 

Color Code =
VAR _v_per = [VTF%] // Assumed it is a measure
VAR _fsmonth = Max(Date[Fiscal Month]) //Assumed it is column and used in vsiaul

RETURN
SWITCH(
TRUE(),
_fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per >= -.05 && _v_per <= .50, "Green",
_fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per > .50, "Yellow",
_fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per < -.05, "Red",

_fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per >= -.05 && _v_per <= .25, "Green",
_fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per > .25, "Yellow",
_fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per < -.05, "Red",

_fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per >= -.05 && _v_per <= .05, "Green",
_fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per > .05, "Yellow",
_fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per < -.05, "Red",

"Other"
)

 

 

Also refer

Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf
Power BI Field Parameters, Keep Axis Sort intact| Always Sort on X/Categorial Axis: https://youtu.be/GfBrB6czByw

 

 

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Amitchandak Can you please modify the code to accommodate the following scenarios:

    Current code you provided formats color when you select ONE fiscal month, whether it’s Jul, or Nov, or Mar, etc.

    But, I would like the code to accommodate the selection of more than 1 fiscal month.

    Assume M1 = 1st fiscal month of a fiscal quarter; M2 = 2nd’ M3 = 3rd, etc.

     

    Example:

    If user selects M1 + M2 (e.g. Oct + Nov), then the color format code should follow the rules as if only M2 were selected.

    If user selects M1 + M2 + M3 (e.g. Oct + Nov + Dec), then the color format code should follow the rules as if only M3 were selected.

     

    Can you modify the code for me? Thanks so much!