cancel
Showing results for
Did you mean:
Resolver I

## Conditional Formatting DAX help

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"

1 ACCEPTED SOLUTION
Resolver I

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:

3 REPLIES 3
Community Support

Hi @ET_Phone2 ,

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.

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

Super User

@ET_Phone2 , 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",
"red"
)

refer

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Resolver I

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:

Announcements