Forum Discussion
Conditional tables slicing in DirectQuery
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?
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"))
5 Replies
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
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:
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"))
