cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chotu27 Member
Member

handling blanks in tables and matrix

Hi All,

 

 

I have blank values in matrix if i replace with '0' and filter with slicer then i see some the not related columns are also shown Zero

Table bLANKS. PNG.PNG

 

 

 

MY DAX Formulae =   IF(ISBLANK([Screened Subjects]),0,[Screened Subjects])

is there any DAX to control this

 

1 ACCEPTED SOLUTION

Accepted Solutions
lndnbrg Member
Member

Re: handling blanks in tables and matrix

To get my understanding right: You want to create a column where you show the values from the Measure COMPLETED. In case there is a BLANK you want to show a 0 instead.

 

But, this will fill all other rows with 0 too! You could first check, if the SCREENED SUBJECTS Measure is BLANK or not.

 

Blank Completed = IF(ISBLANK([Screened Subjects]),BLANK(),IF(ISBLANK([Completed]),0,[Completed]))

 

Does this help?

 

 

9 REPLIES 9
lndnbrg Member
Member

Re: handling blanks in tables and matrix

Hey! You may only show the 0 IF NOT(ISBLANK([Screened])).

In this case it will only show the value in relevant contexts.
chotu27 Member
Member

Re: handling blanks in tables and matrix

@lndnbrgCan you share me the exact calculation please?

Highlighted
chotu27 Member
Member

Re: handling blanks in tables and matrix

Blank Completed = IF(ISBLANK([Completed Assessments]),0,[Completed Assessments])

lndnbrg Member
Member

Re: handling blanks in tables and matrix

Ok, great! I was about to write this to you now. But cool that you figured it out yourself.

chotu27 Member
Member

Re: handling blanks in tables and matrix

@lndnbrg I  Agree that my calculation is correct  but when i filter with slicer extra columns are also showing Zero but they are not related to fields selected from slicer can you let m,e know  how to overcome this issue

Community Support Team
Community Support Team

Re: handling blanks in tables and matrix

HI @chotu27.

 

Can you share more detailed information and sample files? It will be help to clarify your requirement.

 

BTW, I think you need to modify your measure formula. Based on your screenshots, it will calculate thought all your records(include blank records).

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
lndnbrg Member
Member

Re: handling blanks in tables and matrix

To get my understanding right: You want to create a column where you show the values from the Measure COMPLETED. In case there is a BLANK you want to show a 0 instead.

 

But, this will fill all other rows with 0 too! You could first check, if the SCREENED SUBJECTS Measure is BLANK or not.

 

Blank Completed = IF(ISBLANK([Screened Subjects]),BLANK(),IF(ISBLANK([Completed]),0,[Completed]))

 

Does this help?

 

 

Molotch Regular Visitor
Regular Visitor

Re: handling blanks in tables and matrix

This behaviour really surprised me. I was under the impression if I have a slicer active the model is filtered according to the defined relationships and if I convert a value of BLANK to 0 it should only affect the entities in the current filter context. Instead the slicer is overridden and every object is shown in the visualization.

 

I was under the impression you had to expressly override a filter in a DAX calculation if you wanted to change the propogated filter context.

 

Where can I read about the reason for this behaviour?

Molotch Regular Visitor
Regular Visitor

Re: handling blanks in tables and matrix

Another trick is to add +0 in the measure. But this has the same effect. It removes the filter context the slicers provided. I would still love to know why that is. It does not make sense. If the visual truncates dimension rows where the measure is blank the logical result of adding +0 or substituting blank with 0 should be to add the missing dimension rows WITHIN THE CURRENT FILTER CONTEXT, not override the slicer filter and bring in all dimension rows as if the slicer didn't exist.


I actually tried applying the filter in the filter pane as visual, page and report level filter. If I add +0 to the measure no filter (slicer, visual, page, report) will affect what dimension rows the visual show (all of them).

Feel free to pipe in if you have a real understanding of what is going on.