advanced filter breaks measure

[Anonymous]

Let me tell you a story. Follow along with your own copy of PBI Desktop - this will be simple:

I have a table that shows me shipment data. Each row is load that was taken from a source to a destination (identified as a LaneId) by a carrier. I also know something about each load and every record is flagged with true/false value. 

With me so far? My table is called Load and it looks like this:

this is my entire dataset. only 4 columns, 15 loads.

For the purposes of my analysis, I'm interested in Loads flagged as False, so I throw that on a report filter. Now the dataset in context of this report looks like this:

only 7 to look at. Nice!

I'm doing a Lane analysis, so I'm going to have to aggregate this overwhelming volume of data. So I throw together a quick table, summarizing my lanes. Loads = COUNTROWS( Load 😞

We are on the fast track to business insights now!

Excellent. Ah - The business throws me a curveball. I want to summarize Lanes for the "false" loads, but something I need to know is what carriers are taking "True" loads on these same lanes. No problem! CALCULATE allows me to just override the filter context to find those values. So I write this measure:

List Distinct Carriers on TRUE loads = 
CALCULATE( 
    CONCATENATEX( 
        VALUES( Load[CarrierId] ) //list just distinct values in filter context
        , Load[CarrierId]  //concatenate them
        , ","   //comma delimited
    ) 
    , Load[IsSomeBit] = True //blow away that report filter and change it from false to true
)

This should give me a comma delimited list of all the carriers on "True" shipments, even though the report filter is set to "False". It will respect other filters though, so I can still slice and dice by Lane, or whatever else I may want to try!

AMAZEBALLS! Works as advertised.

Looking at my loads in filter context, I see Lane A has got carrier 2 and 4 carrying False loads, so those values showing up truly are the ones on True loads. 

But WAIT - what's that CLEARLY CRAP VALUE in the middle of Lane A? I didn't notice that value in the GIGANTIC VOLUME of data in my dataset.

I go back to my source data and see load abc129. It's a True load, so it's already filtered out of the dataset but that carrier value is obviously not right. After going to the business, they don't want that garbage cluttering their report, but no way are they gonna fix the source system. 

Fine, I guess I'll just filter out that one Load. So I drop LoadId on report filter, and filter where LoadId is not abc129. 

WAIT, NOOOOOOOOOOOO! My wonderful measure just went totally empty! Not a single value. Nothing!

CONUNDRUM, ATTACK!

So that's it - this is my conundrum. Why did adding this one filter break the measure? It's VERY specific, and seems to only care about "is not" filters. I can mess with the filter configurations, and everything works pretty much as expected, UNTIL I start filtering on LoadId. WHY?

Any filter context geniuses out there that can explain what's going on here?

Quick Note: The true reason for my filter is not to filter out the bad "Carrier" value, so a workaround involving dealing with that value is not what I'm after. In fact, just adding the exact same filter to the CALCULATE fixes the issue.

e.g. this actually makes the measure work again: 

List Distinct Carriers on TRUE loads = 
CALCULATE( 
    CONCATENATEX( 
        VALUES( Load[CarrierId] ) //list just distinct values in filter context
        , Load[CarrierId]  //concatenate them
        , ","   //comma delimited
    ) 
    , Load[IsSomeBit] = True //blow away that report filter and change it from false to true
    , Load[LoadId] <> "abc129"  //also, do the exact same thing that the advanced filter is doing(?)
)

What I'm trying to do is understand why it's behaving this way. 

[d_gosbell]

So I have good news and bad news.

The good news is that if I follow your steps exactly I see the same issue.

It does not make a lot of sense to me and I wondered if it was a bug in CONCATENATEX so I tried an alternative syntax of pushing the filters inside the table arguement to CONCATENATEX

eg.

List Distinct Carriers on TRUE loads 2 = 
CONCATENATEX( 
    CALCULATETABLE(
        VALUES( Load[CarrierId] ) //list just distinct values in filter context
        , Load[IsSomeBit] = True //blow away that report filter and change it from false to true
        , Load[LoadId] <> "abc129" 
    )
    , Load[CarrierId]  //concatenate them
    , ","   //comma delimited
) 

This worked and I thought I was onto something... until I started experimenting a bit more. I went back to your original syntax and that was now working too! So then I closed my test pbix file and re-opened it and it still works.

My only theory at the moment is that somehow a certain sequence of steps resulted in a bad cache hit. But I have not seen anything like this before and I'm struggling to reproduce this with anything other than your exact sequence of steps (including trying the advanced filter before editing the measure)

Do you see the same thing? if you close and re-open your pbix file does it start working for you?

[Anonymous]

The re

---

@d_gosbell wrote:

eg.

List Distinct Carriers on TRUE loads 2 = 
CONCATENATEX( 
    CALCULATETABLE(
        VALUES( Load[CarrierId] ) //list just distinct values in filter context
        , Load[IsSomeBit] = True //blow away that report filter and change it from false to true
        , Load[LoadId] <> "abc129" 
    )
    , Load[CarrierId]  //concatenate them
    , ","   //comma delimited
) 

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. 

[d_gosbell · Accepted solution]

---

@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.

[Anonymous]

@d_gosbell 

Very well researched, and articulte answer. I'll have to recreate your troubleshooting steps, and I'm sure to learn a thing or two about DAX in the process.

I'll also have to go back to my production instance and see how this explanation applies. In the example, I removed every possible variable I could think of to keep troubleshooting as straight-forward as possible, but in my real situation, there are more tables, relationships, and more complex interaction with measures. The idea that this only applies to single-table situations causes me to question whether this explains my production scenario, but it absolutely explains the example I posted, so I'm definitely taking this as an accepted solution. If I'm able to re-produce the behaviour without two filters on single table, I'll re-post with a more accurate example.

Thanks for your engagement on this!

[Anonymous]

So, this is weird, right?

Yes, when I open and close, the same problem persists. It's not specific to calculateX, it seems that the filter on loadid is interfering with the context transition of calculate. For some reason, the bitFlag report filter set to False is not being changed by the calculate, and somehow, it's related the [seemingly completely unrelated] filter on loadid. In fact, try selecting just a random sampling of loadids to include via the basic interface, some marked as true, some marked as false. You get the same thing, the calculate measures go blank.

(You should have seen the 45 min process when my colleague brought this to my attention. It rattled my confidence in my DAX abilities, because I wasn't paying attention to that filter. Had to fire up a completely fresh workbook and create the measure from scratch AND SEE IT WORK to start deleting things from the production workbook one at a time to figure out what the root cause was, and this misc. filter sitting off in the corner was THE LAST thing we deleted.)

Simplify - try this:

count true loads = CALCULATE( COUNTROWS( Load ) , Load[IsSomeBit] = true )

mess with the loadid filter. It's not related to the one loadid, or even if that Loadid is marked as true or false. I can try to eliminate any individual load, and the calculate fails to re-set the filter context. 

As I'm typing this, I'm realizing the interface is cross filtering these fields. When I  select False, all the true loadids disappear from interface on loadid filter. I wonder if that is somehow related.