𝐃𝐲𝐧𝐚𝐦𝐢𝐜 𝐂𝐨𝐥𝐨𝐫 𝐂𝐡𝐚𝐧𝐠𝐞 𝐢𝐧 𝐂𝐡𝐚𝐫𝐭 𝐛𝐚𝐬𝐞𝐝 on slicer selection

[evahohk]

Hi all,

I just created a line and stacked column chart. How can I change the colour of last 2 period selected in slicer which is February 2022 & March 2022 to Purple, the remaining period as yellow ? 

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Please create a measure like below, and select the visualization -> go to  Colors -> fx -> format style = field value -> select this measure.

Please check the attache pbix file as well.

Color condition measure: =
VAR _lastdatefromslicer =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR _latestmonthfromslicer =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _lastdatefromslicer ),
        'Calendar'[End of Month]
    )
VAR _priormonthoflatestmonth =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[End of Month] < _latestmonthfromslicer ),
        'Calendar'[End of Month]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Calendar'[End of Month] ) = _latestmonthfromslicer
            || SELECTEDVALUE ( 'Calendar'[End of Month] ) = _priormonthoflatestmonth,
        "Purple",
        "Yellow"
    )

[evahohk]

There are filters in the graph. 

No fx is showing up after I create the condition measure. 

[Jihwan_Kim]

Hi,

Thank you for your message.

Could you please share your sample pbix file's link?
Sorry that I could not see situation that does not show without fx.

Please share your sample pbix file's link, and then I can try to have a look to come up with a more accurate solution.

Thank you.

[evahohk]

Thanks a lot. 

How can I add the colour #8E3A80, #D3DD82 into the color condition measure instead of purple and yellow ? 

[Jihwan_Kim]

Hi,

thank you for your message, and please try the below measure.

Color condition measure: =
VAR _lastdatefromslicer =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR _latestmonthfromslicer =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _lastdatefromslicer ),
        'Calendar'[End of Month]
    )
VAR _priormonthoflatestmonth =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[End of Month] < _latestmonthfromslicer ),
        'Calendar'[End of Month]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Calendar'[End of Month] ) = _latestmonthfromslicer
            || SELECTEDVALUE ( 'Calendar'[End of Month] ) = _priormonthoflatestmonth,
        "#8E3A80",
        "#D3DD82"
    )

[evahohk]

It still didn't work in my file. How can I share the file ? 

[Jihwan_Kim]

HI,

You can share your file's onedrive link, or googledrive link, or dropbox link, for instance.

Thank you.

[evahohk]

Please refer to the link for the file. Thanks.

Test A 

I have tried the colour condition measure in summary. But the colour didn't change for the last 2 periods in the graph. 

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

Sorry that I could not understand your data model, so I changed a little bit like below.

- I created a relationship between two tables.

- I changed the measure for the color condition.

Please check the attached pbix file.

Thank you.

Color condition measure 2: =
VAR _lastdatefromslicer =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            CALCULATE ( SUM ( Summary[CY] ) ) <> BLANK ()
        ),
        'Calendar'[End of Month]
    )
VAR _latestmonthfromslicer =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _lastdatefromslicer ),
        'Calendar'[End of Month]
    )
VAR _priormonthoflatestmonth =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[End of Month] < _latestmonthfromslicer ),
        'Calendar'[End of Month]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Calendar'[End of Month] ) = _latestmonthfromslicer
            || SELECTEDVALUE ( 'Calendar'[End of Month] ) = _priormonthoflatestmonth,
        "#8E3A80",
        "#D3DD82"
    )

[evahohk]

Thanks for your help. I tried to copy the color condition measure to my graph. It only shows 1st column is highlighted. 

Test A.1 

Also is it possible to show the list of period instead of using between function ? 

[Jihwan_Kim]

Hi,

Thank you for your message.

I am not sure if I understood your question fully, but it is difficult for me to know why your data model is using a different column for the axis on the visualization. Without knowing and fully understanding why the specific column is used for the axis on the visualization, instead of a column from the calendar table, it is quite hard for me to fix the measure. May I ask why the column from the calendar table is not used?

[evahohk]

Is there a quicker way to change the period from MMMM YYYY to MMM YY ?
Display e.g. Jan 21 Feb 21 etc

[Dhacd]

@evahohk 
Yes, go to the Data tab and under the column tools ribbon, change the format of the date column to your desired format.

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
Regards,

Atma.

[evahohk]

Thanks. Problem solved. Colour can be displayed now. 

[amitchandak]

@evahohk , Create a measure like this and use in conditional formatting with field value option

new measure
var _max = minx(allselected(Date), Date[Date])
var _min = eomonth(_max,-3)+1
return
if(Max(Date[Date]) <= _max && Max('Date'[Date]) >=_min, "Purple", "Yellow")

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/...

[evahohk]

Thanks for the quick response !

I have added new measure. Then how can I see the changes of the colour in the graph ? 

Also if I know the colour Hex : #B3B7B8, how can I add that into the above formula ?