Forum Discussion
Replacing Blanks with 0 - Adding +0/ creating a new measure not working!
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
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 )ActualM =
IF (
CALCULATE ( SUM ( Expenses[Actual] ) ) = BLANK (),
0,
CALCULATE ( SUM ( Expenses[Actual] ) )
)
Super UserHi 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
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.