Forum Discussion

DouweMeer's avatar
DouweMeer
Icon for Impactful Individual rankImpactful Individual
6 years ago

Implicit filters remove all records?

I've been working on an example from another user. I was initially answerring a question, till I bumped into some weird behavior of Power BI. Source table of the pitched problem:

Request was to have a table with country on rows and on columns users. Then he wanted a value of 'Yes' when the combination exists and 'No' when it doesn't. The solution for him was with a twist as 'blank' was sufficient as a 'No'. Just like below:

But I wanted more. I wanted the 'No' on the blank values. So I continued. So I followed up with:

So my colleague suggested to state all return values as "No" to test the context. 

And I was like 

So apparently when implicit filters (including those from the filter pane) create a context of 0 records, whatever you do with explicit filters or via a DAX expression, will always remain 0. This way when calculation a countrows on an all('table') returns only values when the implicit filters have a context base larger than 0. 

So to screw with my brain even further, first a basic countrows with a filter on 'Country'[Country]:

 

 

And now changing the context to an all statement for the countrows:

 

 

So basically I have only 1 question. What do I need to do to make sure everywhere in this matrix with this source data the blank values are replaced with "No". 

 

Need Help

12 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi DouweMeer 

    it is a veeeeeery looong read )))

    but what I see from the first sught is your try to compare COUNTROWS() with a BLANK().

    COUNTROWS() returns a whole number, so you need compare with 0, not BLANK() like

    if(al=0, "No", al)
    • DouweMeer's avatar
      DouweMeer
      Icon for Impactful Individual rankImpactful Individual
      6 years ago

      az38 

      **bleep** happens when you find something quirky. You can't get away with just but a statement 😁.

      Did tried yours, however the statement =blank() does actually work. See below. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi I would like to test it on my side and if you could share the pbix it would be better.
      I haven't read it everything, but probably when the intercept of two different tables has a blank the measure is blank and therefore your measure is "No" only when you have resulting rows rather thank BLANK() 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        (in any case I gave you one kudo just for the style of the post 🙂 )