cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Display zero instead of blank in matrix

 

Need to dsiplay zero  instead of blank in matrix. Though it looks simple, am struggling to get it done. Heres the sample record.

 

id,name,status

1,a,s

1,b,h

2,d,s

 

Output in power bi. Need to substitute zero in blank cell. tried with isblank , but nothing is working. 

 

=if(isblank(count(id))=true(),0,count(id))

 

name   h s Total

a             1  1

b          1     1

d          1 2  3

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Display zero instead of blank in matrix

@aj1107

 

Hi, you can obtain this view following these steps:

 

1.Create a new Table (Modeling-New Table)

 

Tablepr=Distinct(Table1[pr]

2. Related both tables

 

3. Create the Measure to Count IDs

 

CountIDs=Count(Table[id]) + 0

 

Ready

 

Matrix VisualMatrix Visual




Lima - Peru

View solution in original post

17 REPLIES 17
Highlighted
Super User I
Super User I

Re: Display zero instead of blank in matrix

@aj1107

 

this should work

=if(isblank(count(id)),0,count(id))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Resolver IV
Resolver IV

Re: Display zero instead of blank in matrix

adding + 0 to the value that can contain blank often works

Highlighted
Helper I
Helper I

Re: Display zero instead of blank in matrix

No luck:(  i tried it already.

Highlighted
Super User I
Super User I

Re: Display zero instead of blank in matrix

@aj1107 what isn't working? what did you try?  did you see i changed your formula? (its tried and tested i use it all the time)

 

if you getting an error what is it?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Re: Display zero instead of blank in matrix

=COUNT(TableName[id])+0
Maxim Zelensky
excel-inside.pro
Highlighted
Helper I
Helper I

Re: Display zero instead of blank in matrix

@vanessafvg, Yes. I used the same formula. attached screenshot. Shown the original table view and matrix view. Still get the blank cell after applying measure. 

 

Blank screenshot.png

Highlighted
Community Support
Community Support

Re: Display zero instead of blank in matrix

Hi @aj1107,

 

Since these records not contains in original table, it is hard to show 0 to replace blank value in matrix.

In my opinion, you can try to create a full records table as the source of matrix.

 

Steps:

1. Create full combo records of name and status.

Result Table= CROSSJOIN(VALUES('123'[name]),VALUES('123'[status]))

 

 

2. Use EXCEPT function to remove exist records.

Result Table=
        EXCEPT(
            CROSSJOIN(VALUES('123'[name]),VALUES('123'[status])),
            SELECTCOLUMNS('123',"name",[name],"status",[status]))

 

3. Use SELECTCOLUMNS function to add id column to these missing records and sort the column index.

Result Table=    
SELECTCOLUMNS(
        EXCEPT(
            CROSSJOIN(VALUES('123'[name]),VALUES('123'[status])),
            SELECTCOLUMNS('123',"name",[name],"status",[status])),
    "id",0,"name",[name],"status",[status])

 

4. UNION original records.

Result Table = 
UNION(
    SELECTCOLUMNS(
        EXCEPT(
            CROSSJOIN(VALUES('123'[name]),VALUES('123'[status])),
            SELECTCOLUMNS('123',"name",[name],"status",[status])),
    "id",0,"name",[name],"status",[status]),
    '123')

2.PNG

 

 

5. Create matrix based on above  table.

3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Champion
Community Champion

Re: Display zero instead of blank in matrix

@aj1107

 

Hi, you can obtain this view following these steps:

 

1.Create a new Table (Modeling-New Table)

 

Tablepr=Distinct(Table1[pr]

2. Related both tables

 

3. Create the Measure to Count IDs

 

CountIDs=Count(Table[id]) + 0

 

Ready

 

Matrix VisualMatrix Visual




Lima - Peru

View solution in original post

Highlighted
Frequent Visitor

Re: Display zero instead of blank in matrix

Easy and worthy

 

Thanks

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors