Matrix showing Blanks and Zeroes

[Anonymous]

I have a matrix in a dashboard that's displaying both zeroes and blanks where a count is zero. 

I've identified the situation in which this happens.  Here's the matrix unfiltered, showing the counts for the entire data model:

There are B/Zs in the data model, so when the matrix is filtered down to where none are included, the filtered matrix shows a zero.  But where there are no corresponding data elements in the model (A/Z, E/Z, etc.), the matrix shows a blank. 

The desired outcome is that the matrix displays a 0 in both situations, but I can't find a solution. 

The Values field in the matrix contains the following:

Activities# = count('Activities'[ActivityKey]) + 0

I found code in another post that results in the opposite of the desired result, showing blanks instead of zeroes:

ActivityCountFix = IF (Activities[Activities#] = BLANK() || Activities[Activities#] = 0, blank(), Activities[Activities#])

But when I changed the result from "blank()" to "0", it had no effect:

ActivityCountFix = IF (Activities[Activities#] = BLANK() || Activities[Activities#] = 0, 0, Activities[Activities#])

Any suggestions?

[Bibiano_Geraldo]

Hi @Anonymous ,

Please try the bellow measure:

Activities# = 
VAR vCount = count('Activities'[ActivityKey])
RETURN
IF(
    ISBLANK(vCount),
    0,
    vCount
)

[Anonymous]

This didn't have any effect - the results are the same as the first screenshot in my post.

[Bibiano_Geraldo]

Can your provide a sample dashboard? Make sure to not share sensitive data.

[Anonymous]

No, I can't - it's based on a large, complex data model that contains a lot of sensitive data.  Thanks for trying.

[danextian]

Hi  @Anonymous 

Power BI will show blank if a specific row doesn't exist. For example, in the screenshot below, there are no existing rows for A-N, B-N, C-N, E-N and F-N considering the other filters applied. This still may not show 0 for some cells because there are no rows to add zero to

Activities# = count('Activities'[ActivityKey]) + 0

In the screenshot below, I am using a the same count + 0 measure but you can see that the second table has 0s where the first table has blanks. To achieve this, I created a dimension table for Key2 and relate that to the fact table. The second table in the screenshot uses Key2[Key2] in the column tile.

[Anonymous]

Thanks, I have a couple questions.  Is "Key 2" meant to represent the column headers?  If so, how did you decide which to include and which to exclude?  And if I'm understanding this properly, it requires that the potential values for the column headers be hard-coded into the dimension table?  The column headers are set dynamically based on a slicer and the potential values for the selected field.  If I use this technique, whenever a new potential value is added to the data, the report would need to be manually edited to include the new potential value?  

[danextian]

This is the trick. Create a calculated table that takes distinct values from a column. The values in this column will always exist regardless of the current filter applied to the fact table. In my previous post, Key1[A] and Key2[B] returns 0 because one of they keys exists. However, if you want E-E, you will need to use a dimension table for Key1 as E is not in Key2.

[Anonymous]

Thanks.  I'm still not clear on a couple things.  One, the Key2 table in the screenshot does not include all of the column headers.  The column headers in my screenshots are A, B, C, D, E, and F, but the table in your example only includes A, B, C, and F.  Is there a reason for that?  

Two, I'm not sure what this means:  "In my previous post, Key1[A] and Key2[B] returns 0 because one of they keys exists. However, if you want E-E, you will need to use a dimension table for Key1 as E is not in Key2."  

And lastly, once I create this table, what do I do with it?

[danextian]

As you did not provide a sample, I took the liberty to whip up a random one.  When you add two or more columns in a matrix, Power BI kind of creates a crossjoin of those columns in the background and assigns a value to  to those combinations. Of course, if a combination doesn't exist, Power BI can't assign a value to it.

Once you have created separate dimension tables, relate that to your fact table and cease using the columns from it. Now, if you want all posssible combinations of key1 and key2 to appear in your visual, you will need to combine those columns and then return the distinct values.

Key1 =
//calculated table
VAR _key1 =
    SELECTCOLUMNS ( 'table', "Key", 'table'[key1] ) -- select key1 and name it as "key"
VAR _key2 =
    SELECTCOLUMNS ( 'table', "Key", 'table'[key2] ) -- select key2 and name it as "key"
RETURN
    --combine _key1 and _key2 and return the distinct values only
    DISTINCT (
        UNION ( _key1, key2 )
    )

You can use the same calculated formula for key2.

[Anonymous]

Thanks for your help.  As a relative beginner, I guess there's some more basic knowledge here that I don't have that would help your answer make sense to me.  

[danextian]

Example. Both columns have letters A to E. Power BI will create these combinations in the visual if they're coming from separate dimesion tables. If they are from your fact table, what you will get is whatever combinations there are in your fact table. So if your fact table, doesn't have A-E, you will not see that in the visual as well.