Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

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 matc...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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.

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi Anonymous,

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.