Conditional tables slicing in DirectQuery

[Anonymous]

I have created report with DirectQuery connection and made table with dates when system needs to be checked when date is 12 months or more from current date. And done conditional formating on them so it would show when its red then its >12 months, yellow is >9 months but <12 months and green is <9 months old since last check. This is the formula that works if you create custom column in Import type connection, but I cant use it since report has to be DirectQuery connection. Purpose of this formula is to slice table to "Red","Yellow","Green". That meaning if in the system in any of the date reviews or assessments columns there is Red it will only show thous systems that have least one red column in the row. Even if other columns are green or yellow. For Yellow it will show systems that have least one column that is yellow in any of the four columns. And for Green it will show all systems that dont have any Red or Yellow columns, in short all green row. I can create measure using this formula but not custom column in DirectQuery mode. Anyone know how I could archieve thous slicer functions some other way in DirectQuery type report?

[v-diye-msft]

Hi @Anonymous 

Conditional formatting won't be affected by the slicer selection, the problem might be other else.

Kindly refer to the similar threads for conditional formatting in direct query:

https://community.powerbi.com/t5/Desktop/Conditional-Columns-or-IF-Conditions-in-Direct-Query-mode/td-p/165966 

https://community.powerbi.com/t5/Desktop/Conditional-Formatting-For-Showcasing-machine-status/td-p/248974 

[Anonymous]

I see the the misunderstanding. Slicing would be effecting table as it is supposed to do. I dont expect it to effect conditional formating on itself.

[Anonymous · Accepted solution]

I manage to find the solution to the problem. In the orfiginal formula used for Custom column which has SUM DAX function and the CALCULATE function. Since SUM is not supported in the DIrectQuery mode report. Only in measures. I tried to remove it and the CALCULATE function as well, leaving just the IF function. The formale then works for the DirectQuery mode report. 

Name = IF((System_Review_Flow_V2[CF Date failover review]) >11 || (System_Review_Flow_V2[CF Date of last impact assessment]) > 11|| (System_Review_Flow_V2[CF Date of last security review]) > 11|| (System_Review_Flow_V2[CF Date of last vendor assessment]) > 11, "YES", IF((System_Review_Flow_V2[CF Date failover review]) >8 || (System_Review_Flow_V2[CF Date of last impact assessment]) > 8|| (System_Review_Flow_V2[CF Date of last security review]) > 8|| (System_Review_Flow_V2[CF Date of last vendor assessment]) > 8, "NEARLY YES", "NO"))

[amitchandak]

Not sure I got it. But you can create a color measure and use that in conditional formatting, after using field option

Color Date = if(FIRSTNONBLANK('Date'[date],TODAY()) <today(),"lightgreen","red")
Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
Color Year = if(FIRSTNONBLANK('Date'[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK('Date'[Year],2014)>2018,"red","yellow"))

Color  = if(FIRSTNONBLANK('Date'[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 
,"lightgreen",if(FIRSTNONBLANK('Date'[Year],2014)>2018,"red","yellow"))

Refer: https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...

[Anonymous]

I think your solution is for getting columns to be the color I need them. But that isnt the problem I can still do conditional formating on them. Am I right? Only thing that I cant do is to slice them based on how long ago was the check.