Recently I faced a situation where a simple measure such as COUNTROWS was returning a blank, yeah what's up with that? COUNTROWS shouldn't return a blank if a table has rows. I was so confused.
And it turned out that playing too much with the settings of a slicers in Power BI can generate unexpected results and that's what this blog is about.
Let's say we have this report which shows how many rows are there for each brand in the Products table, for now there is no selection over the slicer because first we need to check how this number is computed with DAX Studio.
So this is the query that is executed by Power BI when nothing is selected:
And for this no xmSQL query is executed to retrive data from Storage Engine, because the DAX Engines already know how many rows are there in Products table and for this they don't need to work too hard. Look at the Logical and Physical query plans, they show Constant, and now sign for the scan of the table.
So you can see that if nothing is selected in the slicer then we get 2517, remember this.
So now if I select everything in the slicer let's see what is executed internally:
So at the surface, everything is working as expected, selecting everything is just like selecting nothing, but internally engine collects all the values and applies them in the where clause to retrieve a data cache of COUNT(*) grouped by Brands.
Now let's make some selections over the slicers to see the internal queries.
This time as well the engine is retrieving the values from slicer and injecting as a filter in SUMMARIZECOLUMNS, and in this filter context, [# Products] measure is evaluated. Pay attention to the highlighted area, there is an IN clause with 5 values in a list this time.
So far everything is normal and as expected, now let's do something that will break the report, so I have duplicated the slicers and have disabled the interaction between slicers.
In the slicer on the right I am going to change some properties:
First turn on "Show Select All option"
If you evaluate the measure with the Select All option then the scenario is same as the first one, the engine doesn't execute any xmSQL queries and there is no TREATAS being used to inject values from slicer.
And this is the code that Power BI executes for the card on the right:
However, now I will go ahead in the settings of the slicer, and turn off Show Select All option as well as Multi Select with CTRL.
And now let's say we don't remember the settings, so we go ahead and uncheck everything in this slicer:
You see the card returns a BLANK! Why? this is what we need to understand.
Let's look at the queries that are getting executed now internally:
Notice there is a NIN (Not IN) clause in the xmSQL? This is what is causing the problem, why is this happening?
In the slicer we unchecked every value of the Brand, however, the Select All option still is checked:
So what happens is there are 2 behaviours
Select Everything - Select All
Select Nothing - We unchecked everything
For selecting everything the engine retrieves the list of values of Brands and for Selecting nothing it executes the NIN clause.
So if we say Italy IN Europe - This will return TRUE but if we say Italy in America - This will be a negation, right?
Because of this NIN clause the engine can't fetch any row from the products table that's why it returns a blank.
So you can see if you do not pay attention to the changes and selection you make in the slicer and settings, you will fall in this trap of Blank value.
And if you don't know using DAX Studio, this can be impossible to understand and solve.