Dax giving blank value even after Isblank is used

[harshadrokade]

HI All,

I have created a DAX with Switch function so that score value gets changed as per standard selected in slicer. I have also used isblank to manage blank error.

There is a case where a one school don't have one standard applciable i.e. School12 don't have standard 4A applicable. When I select School12 in first slicer & standard 4A in second slicer, I should get value as N/A as I have created a measure that if the value is blank, sho it as N/A. But I am getting error as Blank. How can I fix this?

Table1

Column1- Name of School

Column2- Standard

Column3- Subject

Column4- Score

I have two slicers on the dashboard-

1) Name of the school from Table 1

2) Standard from Table 1

Measure to calculate the average score of selected Standard=

SWITCH(

SELECTEDVALUE(Table1[Standard])

,"1A",if(isblank(CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC1"))),"N/A",CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC1")))

,"2A",if(isblank(CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC2"))),"N/A",CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC2")))

,"3A",if(isblank(CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC3"))),"N/A",CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC3")))

,"4A",if(isblank(CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC4"))),"N/A",CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC4")))

,"5A",if(isblank(CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC5"))),"N/A",CALCULATE(AVERAGE(Table1[Score]),FILTER(Table1,Table1[Subject]="ABC5"))))

[v-yanjiang-msft]

Hi @harshadrokade ,

According to your description, I create a sample, when I select “4A” in the first slicer, as its subject is not “ABC4”,the measure shows “N/A”, and the second slicer of school only display schools which standard is “4A”.

Is your method different from mine? Or can you explain more about your sample?

Best Regards,
Community Support Team _ kalyj

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

[PhilipTreacy]

Hi @harshadrokade 

When you select School12 from the first slicer, that should remove 4A from the second slicer, so how have you got this set up? 

Also, without your data it's very hard to recreate data that matches what you have and therefore be able to test things.

Can you please supply some sample data and explain how your slicers/model are set up.  It would be better if you can supply your PBIX with anonymised data.

Regards

Phil