Slicer: AND values with multiple rows. Rows with same id and diferent values

[javi0unavailabl]

Hello!

I have this scenario:

The behaviour desired by selecting two or more options in the Slicer, is showing in the Table only the results that match all the options selected for each id.

Better with an example of the result desired:

In the previous image, the rows with id 3 and 4 is not desired because they have only the group 'A OR B' no both.

In the required case I wanted result of 'A AND B'

I Hope I could successful explained the problem and I don't know how could solve it (or if it is possible to solve).

Thanks for reading!

[Ashish_Mathur]

Hi @javi0unavailabl,

See if my solution helps - Alter the behaviour of a filter/slicer from OR to AND.

[javi0unavailabl · Accepted solution]

Hi @Ashish_Mathur, thank you so much for your reply.

After understanding and testing your solution posted in your website, I have to say that is a very good (and ingenious) solution approach. I lightly have modified some formulas to adapt it to my scenario. I will explain here the solution adopted for me:

Creating two measures:

(Counts te number of selections done in the slicer)

M1 = COUNTROWS(ALLSELECTED(_TEST[group]))

(Distinct because it is contemplated that there are repeated groups for the same id)

M2 = DISTINCTCOUNT(_TEST[group])

Using them together into a new other like this:

Measure = 1*([M1]=[M2])

The result of this Measure is 1 if the id matches all the selections and 0 otherwise. Then, only have to filter the visual with this Measure equal to 1, to change the behaviour of the slicer from OR to AND.

The unique wrong case I found with this solution is when no selections are done. It is because ALLSELECTED returns the entire set when no selections are done.

Waiting to solve the problem with ALLSELECTED, I will mark this reply as solution.

Thanks again.

Regards

--

Javi.

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.  I will try to devote some time later to resolve the minor issue identified by you.

[v-huizhn-msft]

Hi @javi0unavailabl,

I reproduce your scenario and get expected result, please follow the steps below.

1. Create a measure using the formula.

selected_id =
CALCULATE (
    FIRSTNONBLANK ( Test[id], Test[id] ),
    FILTER (
        SUMMARIZE ( ALLSELECTED ( Test ), Test[id], "count", COUNTA ( Test[group] ) ),
        [count] >= DISTINCTCOUNT ( Test[group] )
    )
)

2. Create a visual, select the [selected_id], [group] and [happy] fields. Please see the desired result as follows.



You can donwlload the .pbix file from attachments to check more details.

Best Regards,
Angelia

[javi0unavailabl]

Hi @v-huizhn-msft, thank you so much for your reply!

I tested your solution and works fine with the values A and B, but when I select A and C still remains the id 1 and 2 that they haven't the group C and should not appear, because I want the results that have both selections.

Regards.

Javi.