cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
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:

 

table1.png

 

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.

 

table2.png

 

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

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:

 

thumbnail_image001.jpg

 

View solution in original post

3 REPLIES 3
Microsoft
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
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

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



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!

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:

 

thumbnail_image001.jpg

 

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

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.

Top Solution Authors
Top Kudoed Authors