PowerBi Slicer and Matrix Visualisation Issue

[joeheng123]

Hello I have a problem, where I create a Slicer(Attribute) and select multiple items to display into the Matrix table it shows me blank spaces which I do not want to see as I only want to see the the values selected. I notice that the Matrix filter has been set to "Or". How can I change it to "And" 

For example, when I select 2 subjects on the slicer table (Attribute), it displays blank spaces and values In the Matrix However,i only  want the values to appear.

[AllisonKennedy]

@joeheng123
Slicers are always an OR condition, but you can create a custom measure to get the AND result you want.

See if this post can help you out: https://community.powerbi.com/t5/Desktop/Slicer-with-quot-AND-quot-logic/td-p/592018

If you need it specific to your scenario you will need to provide sample data please.

PS, I am going to move this to Desktop thread where it will get more views and help since it is not related specifically to Power Query. Cheers!

[joeheng123]

Hi,

Attached below is my code however it not working.

For your kind assistance please.

[v-xuding-msft]

Hi @joeheng123 ,

Please try this:

New Table as slicer:

Subject = VALUES('Table'[Subject])

A measure:

Subject with AND-Only Condition = 
var selectedSubject=VALUES(Subject[Subject])
var countRowsFact=
COUNTROWS(
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                'Table',
                RELATED(Subject[Subject]) in selectedSubject
                ),
                "Subject",
                RELATED(Subject[Subject])
        )
    )
)
var countRowsSubject=COUNTROWS(selectedSubject)
return
IF(countRowsFact>=countRowsSubject,countRowsFact)

https://radacad.com/slicer-with-and-condition-in-power-bi#:~:text=The%20default%20behaviour%20of%20t...

[joeheng123]

Hello im having the same issue again i tried numerous times but still unable to change "Or" condition to "And" condition so that my table does not show blank spaces. can anyone assist to provide me the measure code for to copy ?

[v-xuding-msft · Accepted solution]

Hi @joeheng123 ,

You need to drag the measure(Subject with AND-Only Condition) into filter pane and choose "is not blank" then apply filter.

Hope this can help you.

[joeheng123]

Hi i tried the measure code and it does not seem to work do you know how i can resolve this issue?

[v-xuding-msft]

Hi @joeheng123 ,

We need to create a relationship between the two tables while using RELATED function. 

And based on the error message, there is an extra ")" in your formula. Please remove one to try again.

I have attached my sample above. You could download it to have a try.