cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aj1107
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
Vvelarde
Community Champion
Community Champion

@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

18 REPLIES 18
Udheshna
New Member

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
Frequent Visitor

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
Community Support
Community Support

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 as solution to help other members find it more quickly.
hohlick
MVP

=COUNT(TableName[id])+0
Maxim Zelensky
excel-inside.pro
cs_skit
Resolver IV
Resolver IV

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

vanessafvg
Super User
Super User

@aj1107

 

this should work

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

No luck:(  i tried it already.

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@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

Vvelarde
Community Champion
Community Champion

@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

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
Frequent Visitor

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.

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

JohnoP
Frequent Visitor

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.

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

PIC1.png

 

 

PIC2.png

 

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.

 

Hi @aj1107,

 

I think below are you needed.

Source Tables:

8.PNG

 

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

10.PNG

 

Regards,

Xiaoxin Sheng

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

Easy and worthy

 

Thanks

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!