Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

20 REPLIES 20
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.

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

hohlick
Continued Contributor
Continued Contributor

=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

Thank you for providing solution, it worked for me
Anonymous
Not applicable

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.

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
Not applicable

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

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.