Measure for conditional formating with more than 1 condition and using columns from related tables

[Grella]

I am trying to create a measure (i'm not sure if a measure or calculated column is what i should be using) that will ultimately be used purely for conditional formatting. I know how to navigate the conditional formatting settings however i need the measure to use as the "rule".

I have 2 related tables: StoreSales (sales revenue from stores) and dStore (includes store status, open/closed, and store close down date if it is closed etc).

In a matrix table visual i want to conditionally format cells as follows:

- if store status is closed and store close down date is less than sales month, then 1 (trying to visually highlight months when the store closed down eg. Arlington Heights)

- if store status is open and sales month is blank, then 2 (trying to highlight months where there were no sales at all eg. Lakeland)

I've found various solutions online using SWITCH TRUE() or IF statements that look to work in theory but i can't replicate as dax intellisense will not pick up my table columns and will only let me use measures - no doubt I am doing something wrong!

Any help would be appreciated 🙂 

EDIT: Link to pbix and supporting files here

[DataNinja777]

Hi @Grella ,

In my opinion, in order to do it properly, in addition to the store close date field, we need the information of when the store opened so that we can see the duration of the period during which the stores were in operation (to highlight in blue where there were no sales and in yellow if that is after the closure).  Without the opening date information, we cannot determine regarding the past information whether the lack of sales (to be blue highlighted) is due to the store not being open for that month (not starting up) or due to poor performance.  

Best regards,

[Grella]

I have included supporting files with open date data also, appreciate any assistance.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Grella]

thanks for the tips, i have added alink to a pbix file

[lbendlin]

For some reason I have a hard time making the color stick. It disappears when the measure is removed. Maybe someone else can spot my error.

[Grella]

Thanks for your efforts, i see the issue but cannot resolve either