Forum Discussion
Conditional Formatting DAX help
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:
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
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: