Conditional Formatting DAX help

[Anonymous]

Hello all, 

I was hoping someone could help me please. I have a raw data in Excel, that is refreshed every 3 months, detailing training (when it was last done, colour coding showing whether it needs to be redone etc.). 

I also have a small mapping table type thing indicating how often a training needs to be redone but in days (e.g. 365 days is 1 year, 1095 days is 3 years etc.) and this table looks like this:

At the end of the day I'm hoping to have a table a bit like this where based on the above table a colour would get picked with rules:

So if training1 will not expire in the next 90 days = green

if training1 is complete but will expire in the next 90 days = amber

if training1 has never been complete or is out of date = red

etc. etc.

I know there must be some simple DAX sum formula that would work but for the life of me I can't seem to place it. I was thinking something along the lines of a SUMIF table but I'm not sure how to write formula that will say: "If Training1 in the main data matches Training1 in the hierarchy table, and therefore 365 days, do this"

Thanks for all your help! 

[V-lianl-msft]

Hi @Anonymous ,

Sorry, the description you provided and the expected results are difficult for me to understand your logic.

Could you redescribe it? In addition, please paste some sample data to let me know the relationship between the tables and the data in the tables.

Please remove any sensitive data before uploading

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

[amitchandak]

@Anonymous , Create  a color measure, which can use in conditional formatting with "field value" option

Color = if(FIRSTNONBLANK('Table'[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170
,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)

refer

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Anonymous · Accepted solution]

Edited: 

Hi, 

I managed to find a solution to this and the steps I took are detailed below:

Step 1: Create a measure to ensure that I am always looking at the most up to date date Current Date = MAX('Table1'[Date])

Step 2: Ensure I had a relationship set up (Many to One, Single) between my main table and hierarchy table and create the following measure in my hierarchy table Length.1 = SELECTCOLUMNS('Table1', "Length.1", 'Table1'[Length (by days)])

Step 3: In my main table (Table1) create the following measure to work out how many days between the dates in the first measure and today DaysSince = IF(ISBLANK('Table1'[Current Date]), BLANK(),(DATEDIFF('Table1'[Current Date],TODAY(),DAY)))

Step 4: Create another measure to calculate Step 2 minus Step 3 DaysSincevsDaysUntil = CALCULATE('Hierarchy'[Length.1] - 'Table1'[DaysSince],Filter('Hierarchy','Hierarchy'[Column1] = VALUES('Table1'[Column1])), Filter('Name Lookup', 'Name Lookup'[Name] = VALUES('Table1'[Name])))

Step 5: Create a final measure to be used in the conditional formatting

Current Colour = IF('Table1'[DaysSincevsDaysUntil] >= 90,1,

IF('Table1'[DaysSincevsDaysUntil] < 90 && 'Table1'[DaysSincevsDaysUntil] >0,2,

            IF('Table1'[DaysSincevsDaysUntil] <= 0,3)

            )

       )

Step 6: Create a visual using Step 1's measure as the values. Go to conditional formatting and set to the following: