cancel
Showing results for
Search instead 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"

Thanks for all your help!

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
Microsoft

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.

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.

Super User IV

@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",
// Add more conditions
"red"
)

refer

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super 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:

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### Check it Out!

Click here to read more about the April 2021 Updates!

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors