Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mohitkr_01
Frequent Visitor

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

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?

mohitkr_01_0-1617255170304.png

 

Thanks in advance.

1 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.

View solution in original post

13 REPLIES 13
Icey
Community Support
Community Support

Hi @mohitkr_01 ,

 

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

slicer2.PNGslicer1.PNG

 

 

Best Regards,

Icey

 

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

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?

mohitkr_01_0-1618236727331.png

 

Thanks & Regards,

Mohit

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
Impactful Individual
Impactful Individual

@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 : 
HarishKM_0-1617262352717.png

 






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.

mohitkr_01_0-1617264253191.png

 

HarishKM
Impactful Individual
Impactful Individual

@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.  

Here we go.

mohitkr_01_1-1617265545737.png

 

 

HarishKM
Impactful Individual
Impactful Individual

@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 

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
Frequent Visitor

@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, 
 
mohitkr_01_0-1617259228496.png

 

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

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

amitchandak
Super User
Super User

@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))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors