Conditional formatting with filters

[uvil]

Hey all,

I have a Matrix table formed by different zone Groups, that have different % objectives, and when I apply a Conditional Formatting to the table I can't give them different values depending to the zone group,

I mean, the % objectives are different for each group, that's for this, that I can't apply a generic formatting value... And I don't know how to resolve the issue, and maybe, there's an option inside Power BI, that can help me to resolve this problem,

I want something as showed in the link below, and maybe it's difficult to reproduce in Power BI,

Thanks a lot guys!

https://gyazo.com/6a53142423e5d7f99fb6b71b12090f51

[Vvelarde]

@uvil

Hi, you can use a measure as Conditional formatting. In the measure you enter the colors that you want.

Regards

Victor

[uvil]

Hi, 

How can I use the measure as a Conditional formatting, 

Like Zone 1 = MinObj: 30%

                       Obj: %35

       Zone2 = MinObj: 38%

                      Obj: 45%

Thanks!

[Vvelarde · Accepted solution]

@uvil

Hi, try with this:

A measure to apply the color :

Color =
VAR _Sales =
    SUM ( Sales[Sales] )
VAR _Group =
    SELECTEDVALUE ( Sales[Group] )
RETURN
    SWITCH (
        _Group,
        "Group 1", SWITCH (
            TRUE (),
            _Sales <= 0,18, "#ff0000",
            _Sales < 0,21, "#ffa500",
            "#00ff00"
        ),
        "Group 2", SWITCH (
            TRUE (),
            _Sales <= 0,14, "#ff0000",
            _Sales < 0,18, "#ffa500",
            "#00ff00"
        ),
        "Group 3", SWITCH (
            TRUE (),
            _Sales <= 0,24, "#ff0000",
            _Sales < 0,28, "#ffa500",
            "#00ff00"
        )
    )

Note: You can improve this if work with a table and the range color by each group.

Finally , in conditional format Select Format by Field Value and choose the measure (In this case Color)

Ready

Victor

[uvil]

Hey,

After a few hours working with this topic i'll managed to find the solution, 

Thanks a lot! It works 😄

[uvil]

Hey!

Seems that it can work, but when I fill the measure with the values, it returns me a warning, 

"Argument "n" in SWITCH function is required."  If I keep filling the function, the "n" Argument increments, and I can't solve the issue, may be im getting lost at some point.. Thanks a lot guys!

[Vvelarde]

@uvil

Hi, Switch Function is more useful when had 2 or more conditions to evaluate.

I think after your last "; " enter the 2nd condition of GroupZon

Regards

Victor

------------------------------

Hola Veo que también hablas español. La funcion Switch idealmente evalua más de 1 escenario. En tu caso veo que solo estás evaluando cuando GroupZon es 1. Si sólo es eso borra la última ";" . Sino a partir de ahí tienes que ingresar el siguiente escenario a evaluar.

[uvil]

Hi,

Yes yes, that was just a portion of the code, I have this, with more than 50 groups. I add here just 7,.

Color =
VAR _Avisos = SUM(PartesMantAvisos[ID_Procedencia])
VAR _GrupZon = SELECTEDVALUE(PartesMantAvisos[Grup_Zon])
RETURN
SWITCH(
_GrupZon;
"1"; SWITCH(
TRUE();
_Avisos <= 0,30; "#00ff51";
_Avisos <= 0,35; "#ff9a00";
"#ff0000"
);
"2"; SWITCH(
TRUE();
_Avisos <= 0,30; "#00ff51";
_Avisos <= 0,35; "#ff9a00";
"#ff0000"
);
"3"; SWITCH(
TRUE();
_Avisos <= 0,38; "#00ff51";
_Avisos <= 0,33; "#ff9a00";
"#ff0000"
);
"4"; SWITCH(
TRUE();
_Avisos <= 0,30; "#00ff51";
_Avisos <= 0,35; "#ff9a00";
"#ff0000"
);
"5"; SWITCH(
TRUE();
_Avisos <= 0,33; "#00ff51";
_Avisos <= 0,38; "#ff9a00";
"#ff0000"
);
"7"; SWITCH(
TRUE();
_Avisos <= 0,24; "#00ff51";
_Avisos <= 0,28; "#ff9a00";
"#ff0000"
);
"9"; SWITCH(
TRUE();
_Avisos <= 0,21; "#00ff51";
_Avisos <= 0,25; "#ff9a00";
"#ff0000"
);

But I still have the same error: Argument 'n' in SWITCH function is required.

Thanks!

[dedelman_clng]

Here is how I approached it.  I created a measure "Status" and based the conditional formatting on that measure. I will reverse engineering it for you here:

Status = //Look for the highest delta that is > 0, assign status as appropriate
IF (
    [Uptime Delta Max] < 0,
    IF ( [Uptime Delta Tar] < 0, IF ( [Uptime Delta Th] < 0, 0, 1 ), 2 ),
    3
)

Uptime Delta Max =  //Calculate the difference between the display value and a different set value
CALCULATE (
    [Total Uptime %] - MIN ( 'Application'[Total SLA Max] )
)

Uptime Delta Tar =
CALCULATE (
    [Total Uptime %] - MIN ( 'Application'[Total SLA Target] )
)

Uptime Delta Th = 
CALCULATE( 
    [Total Uptime %] - MIN( 'Application'[Total SLA Threshold] )
)

The measure/value that you are displaying would be the equivalent of my "Total Uptime %".  Each application has a different SLA Max, Target and Threshold, and this information is in its own table in the data model.

Hope this helps

David

[dedelman_clng]

I've had to do something similar, where I calculated the variance from the objective in a hidden column/measure, and did conditional formatting based on the hidden column.

This is, of course, assuming that there is some uniformity among the colors as relates to the variance.

If this explanation doesn't make sense I'll try to dig up the report where I did it.

Hope this helps

David