Forum Discussion
Replacing Blanks with 0 - Adding +0/ creating a new measure not working!
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!
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
8 Replies
- dm-p
Super User
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
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