Replacing Blanks with 0 - Adding +0/ creating a new measure not working!

[Anonymous]

Hello, 

I have been trying to replace Blank values as 0 in my matrix visualisation. I have tried using +0 in a new measure and dax functions but no luck. Below is an example of my data and what i am trying to do. Please share your ideas!!

Direction	Category	Date
I	1	17-Jun
B	2	18-Jun
B	3	17-Jun
I	1	18-Jun
I	2	20-Jun
I	4	21-Jun
B	5	19-Jun

I am trying to add values as "0" when the count of category for a specific date is null. 

Thanks in advance!

[dm-p]

Hi @Anonymous,

This is probably a better question to ask in the DAX forum - there's a lot of skilled DAX writers who don't frequent the custom visuals forum, and the DAX forum has a lot more traffic, so it's likely you'll get faster, more targeted assistance.

I can take a look though - can you provide the DAX you're using for your measure, and some information about the fields you're using? I would expect the following to work:

[Count] = COUNT([Category]) + 0

I've just tested this in a matrix where I actually want the opposite to happen, and all blanks are replaced with zeroes, as expected.

If this (or similar) is not working for you then it suggests that there may be an issue with your model or one of the fields you're adding to the matrix, so if you can provide this info then we might be able to dig a bit deeper.

Daniel

[Anonymous]

Hi @dm-p,

Thank you, i will also share the post under DAX Forum. 

I have used the following dax:

count = count([category]) + 0

count = if(isblank(count([category)),0,count([category]))

Child Count = 
VAR rowcount =
    COUNTROWS ( Child )
RETURN
    IF ( rowcount = BLANK (), 0, rowcount )​

count of category = IF(COUNTX(VCCdata,VCCdata[Category]) = 0, 0, COUNT(VCCdata[Category]))

ActualM =
IF (
    CALCULATE ( SUM ( Expenses[Actual] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( Expenses[Actual] ) )
)

[dm-p]

Hi @Anonymous,

Thanks - without further knowledge of your data/model I would expect any of those to work, so I think you'll definitely get better assistance in the other forum, particualary if you can share some more info around this.

Good luck!

Daniel

[Anonymous]

hey @dm-p, 

I have used only the shown fields to create the matrix visualisation. What kind of information should i be sharing because i just tried to do the same for any sort of data i have and i was not successfull. 

Say i used count = count([category])+0 or  count = count([category])+1, only the fields in the table that already has value are getting updated. the fields with blank as value remain the same irrespective of my formulas. 

[dm-p]

Hi @Anonymous - it's likely something specific to how the data model & its fields and/relationships are set up.

The simplest way to get to the crux of the problem (as someone looking in from the outside) is if you can make a copy of your .pbix file available to review. I can download and take a look and advise further.

If this is not feasible, then the next best solution is steps to replicate the data model and some sample data, and we can see if we can get an example working from here.

[Anonymous]

Hi @dm-p 

Please find my sample file at

https://1drv.ms/u/s!Ap-4rMiqmKwTgYJM9jHfBzG8lixDiQ?e=F8JkBo

[dm-p · Accepted solution]

Thanks @Anonymous - that clears things up perfectly.

The simplest way to explain this is that you need related tables to make the measure work how you want, as using values from the same table as the rows and columns in the matrix doesn't quite produce the results you expect.

By using fields from two related tables, Power BI will produce the product of these two fields and resolve the measure correctly.

The only change i made to your model was to add a simple Date table, and link the date column of your example table to it, e.g.:

Separate Date table - notice that I've marked the Date column in your example table as hidden, as this links the tables and shouldn't be used in the visual

Now in the model, I'll swap out the matrix to use the column from this table - now your matrix works using your existing measure e.g.:

I've uploaded a copy of your doc with the change I've made - hopefully this will work so you can download and review.

Date tables are an essential modelling technique for Power BI - here's a good post on the subject for you to review.

It's also adisable to split your model into facts and dimensions if possible. The same team have a good series on this:

• What is a Relationship?
• What is the Cardinality of the Relationship?
• What is the Direction of the Relationship?
• Data preparation; First and Foremost Important task
• What is a Dimension table and why say No to a single big table
• Basics of Modelling in Power BI: Fact Tables

Hope this all helps,

Daniel

[Anonymous]

Hi @dm-p , 

Thank you for your solution! It worked perfectly. 

Also, thanks for the links to resources.