Hide values in field parameters

[sunilbeckham]

Hello.

I have multiple columns in my table that look something like this:

Student ID	Assessment Awareness of Self and environment	Assessment Cognitive Skills	Assessment Language and communication	Assessment Social skills..	Assessment Physical - Fine Motor	Assessment Physical - Gross Motor
1111	3	3	2	3	3	1
2222	na	BLANK	2	1	2	na
3333	2	1	BLANK	3	3	2
4444	BLANK	3	3	2	na	1

I have combined all the 6 Assessment columns in a field parameter 'Assessment Group'. I'm using this field parameter as a slicer and also as the dynamic X axis in a column chart. On the Y axis, I'm doing a simple:

CALCULATE(COUNTA(Student id),Assessment IN {1,2,3})

I want to hide the BLANK and na values from the X axis for all the assessments that are selected from the slicer group. Currently the visual looks like this:

Is there any way to hide the BLANK and na values regardless of which column is picked from the slicer? I tried doing it through the visual level filter but that affects the calculations on Y axis.
Any help on this would be appreciated.
Thanks!

[v-cgao-msft · Accepted solution]

Hi @sunilbeckham ,

Please create a new measure like:

Measure = 
SWITCH(
    SELECTEDVALUE('Assessment'[Assessment Order]),
    0, IF(SELECTEDVALUE('Table'[Assessment Awareness of Self and environment]) IN {"1","2","3"},1),
    1, IF(SELECTEDVALUE('Table'[Assessment Cognitive Skills]) IN {"1","2","3"},1),
    2, IF(SELECTEDVALUE('Table'[Assessment Language and communication]) IN {"1","2","3"},1),
    3, IF(SELECTEDVALUE('Table'[Assessment Physical - Fine Motor]) IN {"1","2","3"},1),
    4, IF(SELECTEDVALUE('Table'[Assessment Physical - Gross Motor]) IN {"1","2","3"},1),
    5, IF(SELECTEDVALUE('Table'[Assessment Social skills..]) IN {"1","2","3"},1)
)

and then use it as visual's filter:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[sunilbeckham]

@v-cgao-msft I have a quick follow up question if you don't mind.
I'm trying to sort the X axis now by column descending so that it's always in the order of 3,2,1. But it doesn't save the order of the axis when I select a different column from the field parameter slicer.
For example, I select 'Cognitive Skills' from the Assessment Group slicer, and I sort the axis on the visual by 'Assessment Cognitive Skills' column descending. It gives me the desired output 3,2,1. But when I select another Assessment from the slicer, It changes the order again and goes back to sorting it by Y axis.
See screenshot below:

Thanks in advance!

[v-cgao-msft]

Hi @sunilbeckham ,

Normally, this needs to be sorted with the help of auxiliary columns, but in this case it doesn't apply.

You may consider using Unpivot for these columns in the PowerQuery editor.

Because the values on this axis originate from the same field, consider using a visual filter. Reference sample files

Best Regards,
Gao
Community Support Team

[sunilbeckham]

Thank you so much for your help. I really appreciate it.

[sunilbeckham]

Hi @v-cgao-msft 
This was brilliant. Thank you so much for your help, I really appreciate it 😀