advanced filter breaks measure

---

Anonymous wrote:

the reason this works is because of the LoadId included in Calculate, which should not be necessary. Original DAX did not have that, only reset filter context on bit field. 

---

So you are right in that it's when you add an extra filter on LoadId that this issue gets introduced. When I also set a page level filter for LoadId <> abc129 the issue comes back. What we are actually "fighting" against here is an optimization called "Auto-Exists" which normally makes things faster, but in this case it's actually reducing the filter context down to LoadIds where IsSomeBit=False, so when you flip to IsSomeBit=True you get an empty list of LoadIds.

You can read more about how auto exists works here https://www.sqlbi.com/articles/understanding-dax-auto-exist/

but, in most cases you don't notice this feature and it just makes your queries faster.

I found this by turning on the performance analyzer in Power BI, then refreshing the visuals and copying the DAX query and pasting it into DAX Studio. Below is the query that Power BI produces:

// DAX Query
DEFINE
VAR __DS0FilterTable = 
    TREATAS({FALSE,
      BLANK()}, 'Load'[IsSomeBit])

VAR __DS0FilterTable2 = 
    FILTER(
    	KEEPFILTERS( VALUES('Load'[LoadId]) ), 
    	'Load'[LoadId] <> "abc129"
    )

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Load'[LaneId], "IsGrandTotalRowTotal"),
      __DS0FilterTable,
      __DS0FilterTable2,
      "Loads", 'Load'[Loads],
      "List_Distinct_Carriers_on_TRUE_loads", 'Load'[List Distinct Carriers on TRUE loads]
    ),
    [IsGrandTotalRowTotal],
    0,
    'Load'[LaneId],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Load'[LaneId]

At first I thought that it was the KEEPFILTERS() part of the filter that was causing this. But I then connected DAX Studio to this data model and manually edited the filters and I found that it did not really matter what I put in the filter, the mere presence of a filter over LoadId (so the __DS0FilterTable2 variable) was causing it to be intersected with the other filter variable. Even replacing this with ALL( Load[LoadId] ) still resulted in a blank calculation result.

I then added a "DumpFilter" measure using DAX Studio (see https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/ ) and I could then see that the filter context generated inside the SUMMARIZECOLUMNS only included LoadIds that had IsSomeBit = False, so when you just flip the filter on the IsSomeBit column you end up with an empty set of LoadId's.

Adding something like:

ALL(Load[LoadId]) 

or:

Load[LoadId] <> "abc129"

which effectively gets expanded to:

FILTER( ALL( Load[LoadId] ) , Load[LoadId] <> "abc129" )

These extra filters "fixes" this behaviour because the ALL() overrides the restriction injected by the Auto-Exists.

Unfortunately I can't see a generic way of working around this behaviour so that any form of exclusion filtering would still work (with this single table model)

The other "problem" here is that the model is a single table and auto-exists only gets applied to columns in the same table. So if you had a Carriers table and excluded CarrierId = 350000 in that table you would not see this behaviour. So possibly with a more complex multi-table model you would not see this issue.