Using BLANK() to Filter Tables and Matrices
I've recently changed my approach to filtering table and matrix visuals and it's led to big performance improvements in the load times of these visuals so I want to share it with you guys. Basically what I've done is stopped in nearly all cases using measures to filter visuals and instead started writing my measures so that they return BLANK() instead of a result I want to filter out. I'm going to explain a bit about BLANK() first, then I'll give an example of how this works.
Blank as a concept
If you have a background with other database programs you'll probably be familiar with the idea that null - the absence of a result - is different from 0 or "" (an empty string). If you're background is mostly Excel then you should be aware that BI treats null (no value in a column) differently from "" which would denote a string with length 0. We don't need to go deep into the weeds about why this is and how null is treated in every case, there are just two important facts to keep in mind:
1. You can filter or return null results in DAX by using BLANK()
e.g. CALCULATE(SUM(Table[Column]),Table[Column2] = BLANK())
or IF(Results[Grade] = "X",BLANK(),Results[Grade])
2. In a Table or Matrix visual if every result in a row resolves to BLANK() then the row is filtered out.
=BLANK() vs ISBLANK(...)
You might have noticed that in the example above I used = BLANK() - that's a quirk of CALCULATE that likes to have equals value in its filter conditions. In other situations for testing to see if a column is blank you can use the ISBLANK([column]) function.
Phantom Blank rows
A related issue is the presence of a blank row in a table visual like this, which can be caused by null values in the column - but not always.
Say you have a Classes table listing all your classes and one of its field is a SubjectID to identify the subject (we would call SubjectID a "foreign key" on the Classes table and the "Primary Key" of the Subjects table). If you have a SubjectID that doesn't exist on the Subjects table then when you put a field from the subjects table on a visual it will create a blank row because it knows you're missing a record.
The best solution for getting rid of this row on the visual is to rewrite the simple student count measure so instead of
Student Count = COUNTROWS(ClassMemberships)
Blank as a concept
If you have a background with other database programs you'll probably be familiar with the idea that null - the absence of a result - is different from 0 or "" (an empty string). If you're background is mostly Excel then you should be aware that BI treats null (no value in a column) differently from "" which would denote a string with length 0. We don't need to go deep into the weeds about why this is and how null is treated in every case, there are just two important facts to keep in mind:
1. You can filter or return null results in DAX by using BLANK()
e.g. CALCULATE(SUM(Table[Column]),Table[Column2] = BLANK())
or IF(Results[Grade] = "X",BLANK(),Results[Grade])
2. In a Table or Matrix visual if every result in a row resolves to BLANK() then the row is filtered out.
=BLANK() vs ISBLANK(...)
You might have noticed that in the example above I used = BLANK() - that's a quirk of CALCULATE that likes to have equals value in its filter conditions. In other situations for testing to see if a column is blank you can use the ISBLANK([column]) function.
Phantom Blank rows
A related issue is the presence of a blank row in a table visual like this, which can be caused by null values in the column - but not always.
Say you have a Classes table listing all your classes and one of its field is a SubjectID to identify the subject (we would call SubjectID a "foreign key" on the Classes table and the "Primary Key" of the Subjects table). If you have a SubjectID that doesn't exist on the Subjects table then when you put a field from the subjects table on a visual it will create a blank row because it knows you're missing a record.
The best solution for getting rid of this row on the visual is to rewrite the simple student count measure so instead of
Student Count = COUNTROWS(ClassMemberships)
Student Count = IF(ISBLANK(SELECTEDVALUE(Classes[Subject])),
BLANK(),
COUNTROWS(ClassMemberships))
SELECTEDVALUE returns the value in the column specific to that row of the table. If it's blank
then the measure returns BLANK() and the row is filtered out
Why is this better?
If you don't have a lot of data and/or your calculations are quite simple then you won't see any benefit over adding a simple filter to the visual for when subject name is not blank. But if you do have a big calculation load, and especially if your situation is more complicated and you've written a measure to return a value to filter you'll notice a big improvement. The reason for this is that it's much more work for the DAX engine to work out the table value, work out a filter value from a separate measure then compare the two, than it is to work out at step one that you don't want to return this result.
Comments
Post a Comment