cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harshadrokade
Post Patron
Post Patron

Dax giving blank value even after Isblank is used

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"))))
2 REPLIES 2
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1637047482771.png

 

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
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors