cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
_JoSi_DA
Frequent Visitor

Showing Zeros instead of Blank not working in Matrix

Hello all,

 

I have only been involved with Power BI/DAX for a short time.

However I am fully aware of this issue has already been discussed a couple of times also in this forum and I have of course already read the related posts, but I still did not get it to work.

Basically I have a very simple data modell (data comes from an SQL data base) with one column including different vehicle types/groups (e.g. cars) and another column indicating the corresponding quantity for the specific type. Basically the records represent different purchase requests (expansion, relacements).

What I want to achieve is a very simple matrix in power bi (similar to a pivot table in excel), in which I have the different vehicle types as rows, the two purchase requests (expansion, relacements) as columns and finally the quantity as values. So in the end I will get something like this:

 

_JoSi_DA_0-1663750063389.png

So far so good. But some of the groups does not contain any data for the different purchse requests, like Truck/Replacement in the expample above, which results in showing a blank cell. Instead I want zeros to be displayed in such cases, which is exactly my problem. Referring to the other posts and solutions what I have done so far are some different version of creating measures as follows, which should alternatively serve as values for the matrix:

 

Quantity Total - Display Zeros = 
var SumQuantityTotal = SUM(KTA_Data[Quantity Total])
return
if(ISBLANK(SumQuantityTotal), 0 , SumQuantityTotal)

 

Referring to this post I also tried using the new Coalese-Function but still the blank value do not show as zero.

 

Sum Quantity = SUM(KTA_Data[Quantity Total])
Quantity Total - Display Zeros = 
COALESCE([Sum Quantity], 0)

 

 

I would be very grateful if someone could help me here. Thank you very much in advance.

Jo 


2 ACCEPTED SOLUTIONS
v-yadongf-msft
Community Support
Community Support

Hi @_JoSi_DA ,

 

This is my test table:

vyadongfmsft_0-1663815566076.png

 

The original matrix:

vyadongfmsft_1-1663815636242.png

 

Please try following DAX:

Measure = 
var sumquantity = SUM('Table'[Quantity])
return 
IF(sumquantity = BLANK(),0,sumquantity)

 

The current matrix:

vyadongfmsft_2-1663815709366.png

Best regards,

Yadong Fang

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

Hi @_JoSi_DA ,

 

OK, I know the reason for the problem.

 

This is my test table, I deleted blank rows:

vyadongfmsft_0-1663832769004.png

 

Please try following DAX:

Expansion = 
var sum_expansion = CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Type] = "Expansion"))
return
IF(sum_expansion =BLANK(),0,sum_expansion)

Relacements = 
var sum_relacements = CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Type] = "Relacements"))
return
IF(sum_relacements = BLANK(),0,sum_relacements)

 

Then create a matrix like below:

vyadongfmsft_1-1663832986837.png

 

Please note: The fields placed in values are the measures just created, not the column in the table.

 

Best regards,

Yadong Fang

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

4 REPLIES 4
_JoSi_DA
Frequent Visitor

Hi @v-yadongf-msft 
first of all thanks for your quick reply.

I tried your DAX, but unfortunatly it still shows the blank cells.

_JoSi_DA_0-1663831056127.png

However, the difference between your test table and my data is, that I do not really have any blank cells in my table, like you have in the last 2 rows (Truck -> Replacement -> blank). So every row in my table contains a real value in the quantity column, but referring to your test table, the last two rows would not be in my table at all.

So this is why I think the IsBlank() does not work properly.


Greetings,
Johannes

Hi @_JoSi_DA ,

 

OK, I know the reason for the problem.

 

This is my test table, I deleted blank rows:

vyadongfmsft_0-1663832769004.png

 

Please try following DAX:

Expansion = 
var sum_expansion = CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Type] = "Expansion"))
return
IF(sum_expansion =BLANK(),0,sum_expansion)

Relacements = 
var sum_relacements = CALCULATE(SUM('Table'[Quantity]),FILTER('Table','Table'[Type] = "Relacements"))
return
IF(sum_relacements = BLANK(),0,sum_relacements)

 

Then create a matrix like below:

vyadongfmsft_1-1663832986837.png

 

Please note: The fields placed in values are the measures just created, not the column in the table.

 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yadongf-msft
Community Support
Community Support

Hi @_JoSi_DA ,

 

This is my test table:

vyadongfmsft_0-1663815566076.png

 

The original matrix:

vyadongfmsft_1-1663815636242.png

 

Please try following DAX:

Measure = 
var sumquantity = SUM('Table'[Quantity])
return 
IF(sumquantity = BLANK(),0,sumquantity)

 

The current matrix:

vyadongfmsft_2-1663815709366.png

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yadongf-msft ,

you are the man 😎 That did the job.
I see I will have to force myself changing my approach when working with Power BI and DAX in the future. However can not quite understand why obviously Blank() seems to work only in combination with the Calculate-Function and not already, when using 'only' the Sum-Function?!

...anyway. Thank you very much for your help 

Greetings,
Johannes

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors