Slicer not functioning the same on two tables - weird bug

[dsj8wksnnckk]

Hi all,

I have a page with the following visuals: Slicer, Table1, Table2

I have the main report containing that page (import version of all data).
When using the slicer in the main report - it works perfectly on both Table1 and Table2

Relationship between the slicer data and Table1 and Table2 IS THE SAME FOR BOTH.

But,

When I use the semantic model (directquery) for another report I have the followin issue:

Same page: Slicer, Table1, Table2 - everything works perfectly UNTIL I apply conditional formatting to any cell values for Table2: then the slicer doesnt slice out the data, but rather applies or doesnt apply the conditional formatting for the sliced rows.

This happens ONLY for Table2. Table1 is working perfectly.

I checked all the interactions - its set up correctly and same for Table1 and Table2 compared to the slicer.

I literally have NO IDEA why is this happening. 

[rajendraongole1 · Accepted solution]

Hi @dsj8wksnnckk - The issue arises because conditional formatting in DirectQuery mode generates additional queries that may not respect slicer filters. The best fix is to use measures for conditional formatting instead of applying it directly to columns, as measures are evaluated within the correct filter context.

Instead of applying conditional formatting directly to a column, use a measure to control the formatting logic. Measures are evaluated in the correct filter context.

ConditionalMeasure =
IF(SUM(Table2[Value]) > 100, "Highlight", "No Highlight")

Use this measure in the conditional formatting rules (e.g., background color).

If Table2 has relationships that are ambiguous or bidirectional, use the CROSSFILTER function to force the slicer context to apply correctly.

MeasureWithFilter =
CALCULATE(
SUM(Table2[Value]),
CROSSFILTER(SlicerTable[Key], Table2[Key], BOTH)
)

[Anonymous]

Hi @dsj8wksnnckk ,

Whether the advices given by rajendraongole1 and Bibiano_Geraldo have solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

[Bibiano_Geraldo · Accepted solution]

Hi @dsj8wksnnckk ,
When conditional formatting is applied, Power BI sends additional queries to the underlying data source to evaluate the formatting rules. This can sometimes interfere with the behavior of filters or slicers, especially in DirectQuery, where query execution is dependent on the semantic model's configuration and the database's response. So If the conditional formatting is based on a measure, ensure the measure respects the slicer's filters.
For example:

MeasureForFormatting = 
CALCULATE(SUM('Table'[Column]), ALLSELECTED('Table'))

[rajendraongole1 · Accepted solution]

Hi @dsj8wksnnckk - The issue arises because conditional formatting in DirectQuery mode generates additional queries that may not respect slicer filters. The best fix is to use measures for conditional formatting instead of applying it directly to columns, as measures are evaluated within the correct filter context.

Instead of applying conditional formatting directly to a column, use a measure to control the formatting logic. Measures are evaluated in the correct filter context.

ConditionalMeasure =
IF(SUM(Table2[Value]) > 100, "Highlight", "No Highlight")

Use this measure in the conditional formatting rules (e.g., background color).

If Table2 has relationships that are ambiguous or bidirectional, use the CROSSFILTER function to force the slicer context to apply correctly.

MeasureWithFilter =
CALCULATE(
SUM(Table2[Value]),
CROSSFILTER(SlicerTable[Key], Table2[Key], BOTH)
)