Replacing Null/ blank with 0 using measure affected filtering of Matrix by date table

[mohitkr_01]

Dear Community,

While working on one of the requirement where I wanted to replace Blank values with 0 using measure yielded unexpected result when I am using matrix. 

1st Matrix uses Actual, Budget & forecast directly from table. Some values are Null. When I am using slicer to filter the data based on year (like 2019, 2020, 2021) only data [ertaining to these years are being shown, which is as expected.

Now the requirement is to show 0 instead of Blanks in Actual and Budget fields. so I used 2 measures mentioned below in 2nd table 

sumActual = if(ISBLANK(SUM('Table'[Actual])),0,sum('Table'[Actual]))

sumBudget = if(ISBLANK(SUM('Table'[Budget])),0,sum('Table'[Budget]))

It is replacing values as expected but when I am going up in date hierarchy(Rolling Up to year), and selecting a year(say 2020), the matrix shows all the year having Actual and budget as ) forecast as blank and correct values fro selected year.

I want the matrix to behave like 1st table (show the value for selected year only) along with 0 instead of blanks.

How can I achieve that?

Thanks in advance.

[Icey]

Hi @mohitkr_01 ,

I can't reproduce your issue. Please check if I ignore anything:

Best Regards,

Icey

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

[mohitkr_01]

Hello

Thanks for Sharing the report, It was able to get the expected result by using the date from Date(Date Dimension) table in the matrix.

To reporduce the issue you can use the Date from 'Table' (The fact table) as Matrix Column.

I noticed that there is an extra row with no Expense values but have 0s in Actual, Budget and Forecast.

Although I have excluded it from Matrix but do yu have any idea why that's happening?

Thanks & Regards,

Mohit

[Icey · Accepted solution]

Hi @mohitkr_01 ,

For the initial issue, I have tested for a long time, but can't find any solutions for this issue. This may caused by the back-end operating mechanism - SUMMARIZECOLUMNS function.

You can get the query by "Performance Analyzer" -> "Start Recording" -> "Refresh visuals" -> Copy query of your matrix.

In your scenario, the filterTable is generated from column 'Table'[Date] and the groupBy_columnName is 'Date[Date]'. The filterTable value doesn't contain in the groupBy_columnName.

Therefore, the only way we can do is to use the same column in matrix and slicer. Just as what I set in my initial .pbix file.

And for the blank "expenses", it is caused by the one-to-one relationship, not sure the specific reason. But you can change the relationship to One('Date'[Date])-to-Many('Table'[Date]). Then the issue will disappear.

Best Regards,

Icey

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

[HarishKM]

@mohitkr_01  Hey Mate,

Try this measure = 

null to 0 = IF(SUM(Sheet1[Budget])= BLANK(),0,SUM(Sheet1[Budget]))
Note - Replace your measure name with mine .
Result in picture : 

[mohitkr_01]

Measure's name doesn't influence its result result, even though I gave it the try.

I have selected 2020 in slicer but matrix shows fields for 2019 and 2021.

[HarishKM]

@mohitkr_01 Can you share the screenshot of modeling view . I wanted to  check . Is there any relationship in between your both table . It seems like there is a bad model working in your report 
And yes name does not change but it will ease someone life when you are  not working on that report .

Cheers.  

[mohitkr_01]

Here we go.

[HarishKM]

@mohitkr_01 This will happen one to one relationship.I will suggest .
Kindly 2 new col. in table .
Year = Year (table [date])
Month = Format(Table[date],"Mmm-yy")
Then try .
i hope it will work for you 

[mohitkr_01]

I think this measure is equivalent to, sumActual = if(ISBLANK(SUM('Table'[Actual])),0,sum('Table'[Actual]))
I got the same result as previous by using " null to 0 = IF(SUM(Sheet1[Budget])= BLANK(),0,SUM(Sheet1[Budget]))" 

[mohitkr_01]

@amitchandak : I tried the measure, I modified it as below:

SumofAct =

var act = sum('Table'[Actual])+0

var mindt = MINX(allselected('date'[Date]),'date'[Date])

var maxdt = MAXX(ALLSELECTED('date'[Date]),'date'[Date])

return

CALCULATE(if(max('date'[Date]) < mindt || max('date'[Date])> maxdt, BLANK(),act))

But it has not replaced null but is introducing another blank column but is showing data for relevant year, 

[amitchandak]

@mohitkr_01 , Just want to check the year in slicer and year in matrix visual, both coming from date table?

[mohitkr_01]

No the date in matrix is from Fact table but the year in Slicer is from Date dimension.

[amitchandak]

@mohitkr_01 , if you use isblank or +0 measure, it makes a left join and shows all values.

This is example, what I end up doing to control it with in range , when I am using a date tbale

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))