handling blanks in tables and matrix

[chotu27]

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

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

is there any DAX to control this

[lndnbrg]

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

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

[chotu27]

@lndnbrgCan you share me the exact calculation please?

[chotu27]

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

[lndnbrg]

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

[chotu27]

@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

[lndnbrg · Accepted solution]

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?

[v-shex-msft]

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

[Molotch]

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]

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.

[rubymaya]

Hi ,

I have the same issue as well.

I put 0+ just to make my measure 0 when blank.

But it will brings all rows which are not in the relationship at all. Weird.

Have you found solution for this?