Forum Discussion
Measure for conditional formating with more than 1 condition and using columns from related tables
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
6 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523 - 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,
I have included supporting files with open date data also, appreciate any assistance.
