Conditional coloring by rank not applying to time-series chart

[jaryszek]

Hi everyone,

my model looks like:

I’m using the following measures to color visuals dynamically based on ranking:

Rank (Current Axis) =
VAR Result =
    IF (
        ISINSCOPE ( fct_amortizedcosts[MeterSubCategory] ),
            RANKX (
                ALLSELECTED ( fct_amortizedcosts[MeterSubCategory] ),
                CALCULATE ( [Total Amortized Cost] ),
                ,
                DESC,
                DENSE
            ),
        IF (
            ISINSCOPE ( fct_amortizedcosts[MeterCategory] ),
            RANKX (
                ALLSELECTED ( fct_amortizedcosts[MeterCategory] ),
                CALCULATE ( [Total Amortized Cost] ),
                ,
                DESC,
                DENSE
            ),
            BLANK ()
        )
    )
RETURN Result

Color by Rank =
VAR r = [Rank (Current Axis)]
RETURN
SWITCH (
    TRUE(),
    ISBLANK ( r ) || r > 5, "#B3B3B3",
    r = 1, "#E81123",
    r = 2, "#107C10",
    r = 3, "#0078D4",
    r = 4, "#FFB900",
    r = 5, "#8E8CD8"
)

This works perfectly in donut and bar charts, but when I try to apply the same color logic to a Line and clustered column chart (with Date on the X-axis), all columns turn gray:

Has anyone managed to make field value–based coloring work on a time-series chart when the rank is calculated by category (MeterCategory) and not by the date axis?

Example mode, use Problem to solve page:
https://drive.google.com/file/d/14lpEWK-MQ6JMU5_15WVh8Gv22AiGvdpR/view?usp=drive_link

I think that MeterCategory column does not exist in specific visual and this is why visual does not read it as ISINSCOPE function. 
I tried with HasONeValue but the same i think it is in visual level, not page level filter context...how to pass this ?

Thanks for any suggestions!

[Kedar_Pande]

Create a separate measure that preserves the category context:

Time Series Color =
VAR CurrentCategory = SELECTEDVALUE(fct_amortizedcosts[MeterCategory])
RETURN
IF(
NOT ISBLANK(CurrentCategory),
[Color by Rank],
"#B3B3B3"
)

Use this measure for conditional formatting in your time-series chart. It maintains the category context that gets lost in the date grouping.

[jaryszek]

Thank you. It is closer to finishing it, but it still is not working.

In chart I put:

but still within Color by Rank I am refering to 

VAR r = [Rank (Current Axis)]

which is using IsInScope: 

Best,
Jacek

[v-hjannapu · Accepted solution]

Hi @jaryszek,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to @Kedar_Pande , @FarhanJeelani  for those inputs on this thread. 

The issue is happening because in the Line and Clustered Column chart your X-axis is Date, and Meter category  is not part of that visual. So when Power BI runs the measure, the function ISINSCOPE Meter Category returns FALSE since that field is not in the  visual context.

Because of this, your Color by Rank measure doesn’t get any category value, and all bars turn grey. This is why the logic works fine in visuals where Meter category is on the axis, but not in the time-series chart.

If you really need this feature in time-series charts, I would suggest raising a new idea in the Power BI Ideas forum. If it gets more votes, the Microsoft team will review it and may include it in future updates.
https://community.fabric.microsoft.com/t5/Fabric-Ideas/idb-p/fbc_ideas

Best regards,
Community Support Team.

[v-hjannapu]

Hi @jaryszek,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[FarhanJeelani]

Hi @jaryszek ,

do you want something like below screenshot?

[jaryszek]

Nope, this can be achieved by adding MeterCategory to the Legend. It is native Power BI behavior which sucks badly because I can not set up column colors through conditional formatting.