Show 0 instead of blank when there is no record in the table

[Srikanth7Gunnam]

Hello, 

I am working on a requirement where I need to show '0' instead of blank (when there is no record in the dataset). Here is the sample data set.

StudentCourseScore

A	Maths	50
A	Science	40
B	Science	40
B	History	30
C	Maths	60
C	Social	20

I need to show this data in a matrix visual where if there is a blank I need to show '0'.

Expected Result:

Could you please let me know how this can be done?

[audreygerred]

Try this trick

https://dataap.org/blog/2022/06/14/power-bi-replace-blank-with-0-matrix-visual/#:~:text=To%20replace.... 

[Srikanth7Gunnam]

Thank you!! mlsx4 shared the same trick. It is working but filtering is not working. For instance, if I have slicers from the base table it is not filtering the courses. All the columns are showing with 0's. Any idea how to fix it?

[v-xinruzhu-msft · Accepted solution]

Hi @Srikanth7Gunnam 

You can create a calculated table first

Table 2 = var a=SUMMARIZE('Table',[Student])
var b=SUMMARIZE('Table',[Course])
return GENERATE(a,b)

Then create a measure

Measure =
VAR a =
    CALCULATE (
        SUM ( 'Table'[Score] ),
        FILTER (
            'Table',
            [Student]
                IN VALUES ( 'Table 2'[Student] )
                    && [Course] IN VALUES ( 'Table 2'[Course] )
        )
    )
RETURN
    IF ( a <> BLANK (), a, 0 )

Finally, put the field of table 2 and the measure to the matrix, the put the field of table2 to the slicer

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mlsx4]

Hi @Srikanth7Gunnam 

You can use a var and then, check with a condition

Measure= 

var score= SUM(MyTable[Score])

return if(ISBLANK(score),0,score)

Or if I'm not wrong, you can add a "+0" to the formula

Measure= SUM(MyTable[Score])+0

[Srikanth7Gunnam]

Thank you for your response!!

I have tried both ways before but it is not giving me the expected result. If you are getting the expected result, could you please share the pbix file?

[mlsx4]

Ok, sorry @Srikanth7Gunnam . I think it would work. I have tried now and it is not working. Then, you should do the following approach:

1) Create a new table with the Courses: 

CourseTable= VALUES(MyTable[Course])

 

2) Now, create the measureMeasure= SUM(MyTable[Score])+0 

Measure= SUM(MyTable[Score])+0

3) And then, in the matrix use for the column "Courses" but from the CoursesTable

[mlsx4]

Hi @Srikanth7Gunnam!! Did it work?

[Srikanth7Gunnam]

Hello, Thank you!! It did work but filtering is not working. For instance, if I have slicers from the base table it is not filtering the courses. All the columns are showing with 0's. Any idea how to fix it? 

[mlsx4]

Hi @Srikanth7Gunnam 

Filtering is working (if you select History, it will only show B which is the one with value <>0), but the thing is that Power Bi doesn't show a value when it is null (or blank, which is the same). 

[Srikanth7Gunnam]

Yes, filtering is working but it is showing the courses with 0 when it is filtered. Here is an example.

Dataset:

StudentCourseScoreClass

A	Maths	50	Class A
A	Science	40	Class A
B	Science	40	Class A
B	History	30	Class B
C	Maths	60	Class A
C	Social	20	Class A

I have built a visual like below based on the above data and has a Class slicer. When nothing is selected it is shown like this

But, when Class A is selected it shows History course as well with 0's 

Here is the expected result I am looking for 

Can you please help me with this?

[Idrissshatila]

Hello @Srikanth7Gunnam ,

can you share the result you're having which not matches your need.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[Srikanth7Gunnam]

Hello, thank you for your reply. Yes, I have shared it in my original question. Could you please check ?