Forum Discussion
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?
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:
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]))
By the way, PBIX file as attached.
Best regards,
11 Replies
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
- Measure = IF(ISBLANK(SUM('Table'[Faculty Rank])),0,SUM('Table'[Faculty Rank]))
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'
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?
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 🙂
