Replacing blank with zero for a mesure gives som unexpected extra rows

Question

I have a little puzzle that annoys me in PowerBI/DAX. I'm not looking for workarounds - I am looking for an explanation of what is going on. I've created some sample data to reconstruct the problem.

Here are my two sample tables written in DAX:

Events = 
DATATABLE (
    "Course", STRING,
    "WeekNo", INTEGER,
    "Name", STRING,
    "Status", STRING,
    {
        { "Python", 1, "Joe", "OnSite" },
        { "Python", 1, "Donald", "Video" },
        { "DAX", 2, "Joe", "OnSite" },
        { "DAX", 2, "Hillary", "Video" },
        { "DAX", 3, "Joe", "OnSite" },
        { "DAX", 3, "Hillary", "OnSite" },
        { "DAX", 3, "Donald", "OnSite" }
    }
)


WeekNumbers =
DATATABLE ( "WeekNumber", INTEGER, { { 1 }, { 2 }, { 3 }, { 4 } } )

I have a table with events and another table with all weeknumbers and there is a (m:1) relation between them on the weekNo/weeknumber (I've given them different names to easily distinguish them in this example). I have a slicer in PowerBI on the weeknumber. And I have a table which shows aggregation and counts the participants based on the status with the following measures:

#OnSite = COUNTROWS(FILTER(events,Events[Status]="OnSite"))

#Video = COUNTROWS(FILTER(events,Events[Status]="Video"))

I visualize the two measures in a table together with the Course and the weekNo. With the slicer on weekNumber 3 there are nobody with status video so #video is blank. See screenshot.

Then I decided to create a new measure which should show a 0 instead of blank for the #video:

#VideoWithZero = VAR counter=COUNTROWS(FILTER(events,Events[Status]="Video"))
RETURN IF(ISBLANK(counter),0,counter)

I add the #VideoWithZero to the table and get a lot of extra rows in the table for the other weekNo's:

So my question is - Why do I get the extra rows for week 1 and 2 in the table? I would expect my filter on WeekNumber to filter them out.

Joao Leal Joao Leal · Accepted Answer · 2020-11-11T00:58:01

The filter is being applied to the context of the query executed, and then the measures are calculated. Now the issue is that one of your measures is always returning a value (0), so regardless of your context it will always show a result, thus making it seem that it is ignoring the filter.

One way I tend to get implement this is by providing some additional context to when I might want to show 0 instead of blank. In your case it would be when one of the counts is not blank:

#OnSite = 
VAR video = COUNTROWS(FILTER(events,Events[Status]="Video"))
VAR onsite = COUNTROWS(FILTER(events,Events[Status]="OnSite"))
RETURN IF(ISBLANK(video), onsite, onsite + 0) //+0 will force it not to be blank

#Video = 
VAR video = COUNTROWS(FILTER(events,Events[Status]="Video"))
VAR onsite = COUNTROWS(FILTER(events,Events[Status]="OnSite"))
RETURN IF(ISBLANK(onsite), video, video + 0)

So on the OnSite measure it will check if there are Videos and if so, it adds +0 to the result of the OnSite count to force it not to be blank (and vice versa)

One other way could be to count total rows and subtract the ones different to the status you need:

#OnSite = 
VAR total= COUNTROWS(Events[Status])
VAR notOnsite = COUNTROWS(FILTER(events,Events[Status]<>"OnSite"))
RETURN total - notOnsite

#Video = 
VAR total= COUNTROWS(Events[Status])
VAR notVideo= COUNTROWS(FILTER(events,Events[Status]<>"Video"))
RETURN total - notVideo

Replacing blank with zero for a mesure gives som unexpected extra rows

1 Answers