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

Replacing null values in matrix visual

Hi, 

 

I created a table using the matrix visual. My values are sums of number of records in a field (named 'Faculty Rank' and once I drag that field under values, I choose the Count option). I want to replace the blanks in the matrix with '0' but cannot find any posts on this besides creating a new column in Query editor to the data source. However, because these are counts of the field and I am creating over 100 frequency tables using over 20 fields, I figured there's an easier way to do this?

 
1 ACCEPTED SOLUTION

Hi @elopez ,

 

Based on my research, If your table does not have at least one row record in the blank cell (such as our following example), the measure will not calculate at all:

 

9.jpg10.jpg11.jpg

 

We can use a separated table such as following to meet your requirement

 

Calculated Table:

 

Column = DISTINCT('Table'[Column])

 

 

Measure:

 

Measure Value = CALCULATE([Measure]+0,'Table'[Column] in DISTINCT('Column'[Column]))

 

 

12.jpg13.jpg

 


By the way, PBIX file as attached.


Best regards,

 

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

View solution in original post

10 REPLIES 10
bheepatel
Resolver IV
Resolver IV

Hi @elopez 

 

I would suggest to create a new column with the following DAX function to replace the blank values:

 

New Column = IF( Old Column = BLANK(), "0", Old Column)

 

You can then choose the New Column in the Matrix visual.

 

If this does not work, I would suggest you share some screenshots 🙂

 

Greg_Deckler
Super User
Super User

Measure = IF(ISBLANK(SUM('Table'[Faculty Rank])),0,SUM('Table'[Faculty Rank]))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I am new to power BI so maybe i did this wrong? I: clicked the table-->New measure-->wrote in what's in the screenshot-->dragged the newly created 'number of faculty' measure to my values field. but then the table couldn't be displayed (the table is very similar to the 2nd table below it. i want to replace all the blank values with '0'

 

Capture.PNG

 

so i created the measure and named it number of faculty (from your first message). then i dragged that to the values section. is this correct and the problem is the string type?

 

Capture2.PNG

Wait, is that measure you are pointing to what is being used in your current table visualization? If that is the case:

 

Measure = IF(ISBLANK([your current measure name]),0,[your current measure name])

 

Can you click on See details and tell us what it says?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

based on your 2nd message, i am unsure if i need to change what i wrote in the original measure?

Hi @elopez ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

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

Hi @elopez ,

 

Based on my research, If your table does not have at least one row record in the blank cell (such as our following example), the measure will not calculate at all:

 

9.jpg10.jpg11.jpg

 

We can use a separated table such as following to meet your requirement

 

Calculated Table:

 

Column = DISTINCT('Table'[Column])

 

 

Measure:

 

Measure Value = CALCULATE([Measure]+0,'Table'[Column] in DISTINCT('Column'[Column]))

 

 

12.jpg13.jpg

 


By the way, PBIX file as attached.


Best regards,

 

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

I tried it and it doesn't do anything:

 

 
amitchandak
Super User
Super User

@elopez 

can not say without looking at data and formula.

But Typically we add + 0 to the formula to deal with it

 

Measure = calculate(<Formula>) +0

or Coalesce( calculate(<Formula>),0)

or Coalesce( calculate(<Formula>),0)  +0



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.