Display zero instead of blank in matrix

[aj1107]

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

[Udheshna]

Hi,

   I badly need help with a slightly different scenerio. I am trying to show values within a matrix and replcae blank values to 0 and there ia also an option to be provided for the client to extract underlying data. using the solution shared here by vvelarde blanks are being replaced to 0 but my excel extract is not filtering the data accordingly (Bth the tables are related).

Is there any other way to acheive 0 instead of blanks in matrix and allowing the client to extract underlying data.

Thanks in advance.😊

[magicbiman]

One way this may be able to be achieved just using the Power Query Editor interface, is to Pivot the table on the "name" column (your selected matrix row parameter) using count of "Id" as the value. Replace "null" values with 0 in the resulting table, then select any non-name value columns (status) and Unpivot Other Columns.

The result should be your original table but with an entries containing 0 where no row previously existed. You can then create a matrix using your new "fully populated" record table and 0's should show where no values were present before.

It worked well in my scenario although I have no instances where a "name" would appear more than once against an "id". So the pivot action aggregating as sum simply returns the original value. Haven't given a lot of thought as to whether this would work as expected if this wasn't the case.

[v-shex-msft]

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

[erikvolk]

Thanks so much. This was the only solution that worked for me!

[hohlick]

=COUNT(TableName[id])+0

[cs_skit]

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

[vanessafvg]

@aj1107

this should work

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

[cuscuna]

This is exatly what I have been searching for.  Thank you!

[aj1107]

No luck:(  i tried it already.

[vanessafvg]

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

[aj1107]

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

[Vvelarde · Accepted solution]

@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 Visual

[murali_m]

Thank you for providing solution, it worked for me

[Anonymous]

Hi,Related both tables means how  i want to do in modelling tab or through dax?

and what cross filter direction i want to choose.

[Subhraz]

@ Vvelarde

This is a great solution. In my case in the matrix few cells are empty as there's no correspoding values coming from source DB.

Even NULL is not coming. If Null comes then BLANK() function works. Nothing worked till now no matter what I did. Finally this is simple trick worked. Thanks a lot.

[Anonymous]

This resulted in a circular dependency for me. Do you know why or how to get around it?

[JohnoP]

Used the +0 recommendation from this post some time ago.

Just noticed though that this does cause filters (on the page) to stop working. Just a kind warning.

[aj1107]

Hi, I need your help to handle below scenario. I tried to apply the logic using lookup table, however the zero is displayed for all the values in lookup table.

Relationship Lookup(Column1)->Lookup(Lkp)

Calculated measure - #cnt=count(id)+0

Txn                                                                               Lookup

Since ther matrix is based on lookup table,  zero is displayed for all the lookup values(E-H). Isit possible to restrict rows if all the columns in matrix is zero(not to display). So that slicer by lookup shows only corresponding values in matirx. Right now it dispalys all the lkp values in rows irrespective of Txn values.

Thank you.

[v-shex-msft]

Hi @aj1107,

I think below are you needed.

Source Tables:

Formula:

Analysis = SUMMARIZE(Lookup,[Lkp],"Open",SWITCH(LOOKUPVALUE(Txn[State],Txn[Name],[Lkp]),"Open",1,0),"Closed",SWITCH(LOOKUPVALUE(Txn[State],Txn[Name],[Lkp]),"Closed",1,0))

Regards,

Xiaoxin Sheng

[hmedinaa]

Easy and worthy

Thanks