cancel
Showing results for
Did you mean:
Highlighted
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

## 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

Matrix Visual

Lima - Peru
17 REPLIES 17
Highlighted
Super User I

## Re: Display zero instead of blank in matrix

@aj1107

this should work

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

Proud to be a Super User!

Highlighted
Resolver IV

## Re: Display zero instead of blank in matrix

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

Highlighted
Helper I

## Re: Display zero instead of blank in matrix

No luck:(  i tried it already.

Highlighted
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?

Proud to be a Super User!

Highlighted
MVP

## Re: Display zero instead of blank in matrix

`=COUNT(TableName[id])+0`
Maxim Zelensky
excel-inside.pro
Highlighted
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.

Highlighted
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')```

5. Create matrix based on above  table.

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

## 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

Matrix Visual

Lima - Peru
Highlighted
Frequent Visitor

## Re: Display zero instead of blank in matrix

Easy and worthy

Thanks

Announcements

#### 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

Learn about the exciting things that happened in July.

#### 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.