Multiple Slicers with Multiple Selection in each with AND condition

[Vigneshkrish333]

Hi, I am working on a model that filters items based on the criteria given in the slicers. In this I want to filter the item through three slicers

1. Hierarchy Slicer
2. Attribute Slicer
3. Attribute Value Slicer. As an example, let's say in second slicer I want to filter an item using two attributes (STANDARD, FINISH) with values (ISO 4014, NO SURFACE TREATMENT). Meaning I want to filter (see) the item which has ISO 4014 as STANDARD and  NO SURFACE TREATMENT as FINISH. But when I try to do this (refer screen shot) it is not filtering for the 'AND' condition. It is filtering for 'OR' condition (i.e it filters all materials which has either NO SURFACE TREATMENT as FINISH 'OR' STANDARD as ISO 4014, which I don't want. I want to filter the material which has both NO SURFACE TREATMENT as FINISH and ISO 4014 as STANDARD.

   How can I achieve my requirement?

[Vigneshkrish333]

@amitchandak  Hi amit, I am new to PowerBi. I have referred you youtube video https://www.youtube.com/watch?v=X5T4rIZovHk&t=192s wich resemble my scenario. Can you please help me in solving this issue?

[Vigneshkrish333]

Hi @Anonymous Can you please put the step by step input for attaining the same result by measure Itself instead of Measure 2? Because I don't want to create multiple mini slicers like 1, 1.1, 2, 2.1 etc., My slicers are limited as in the image.
Currently My PowerBi screen is like this

[Anonymous]

Hi,@Vigneshkrish333 

Here are a few more detailed steps of the solution we gave you in our first post:

1.First I created the following calculation column combining "UNSPSC Attributes Values" and "UNSPC Attributes" to give you all the options for your requirements:

Column = 'Table'[UNSPC Attributes]&" "&'Table'[UNSPSC Attributes Values]

2.I then create a calculation table that implements a reference to the calculation columns from the previous step:

Table 2 = SELECTCOLUMNS('Table',"1",'Table'[Column])

3.I then used column "1" in Table 2 as a slicer:

4.The following measure are then created:

Measure = 
VAR select1=VALUES('Table 2'[1])
VAR number=IF(NOT(ISFILTERED('Table 2'[1])),2,CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),'Table'[Column] IN select1&&'Table'[Material]=MAX('Table'[Material]))))
RETURN IF(number=2,1,0)

5.Reference the measure in a table visualization object:

6.Modify the filter for this visual object and apply:

7.Finally, remove the measure:

8.Here are the final results:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vigneshkrish333]

Hi @Anonymous I understood your solution but from the slicer available in your attached pbix file, while selecting the FINISH ISO 4014 from slicer 1, why the table is not showing all the records which has ISO 4014 as value? Ideally it should show 70001701 and 70001700 right? but table shows blank why?

[Vigneshkrish333]

@Anonymous  image for reference

[Anonymous]

Hi,@Vigneshkrish333 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Best Regards,

Leroy Lu

[Anonymous]

Hi,@Vigneshkrish333 

Thank you for your quick reply, so that I can better understand your needs.

Regarding the issue you raised, my solution is as follows: 

1. The following is the measure I updated according to your needs, hoping to meet your needs:

MEASURE = 
VAR select1 =
    VALUES ( 'Table 2'[1] )
VAR selectnumber =
    IF ( ISFILTERED ( 'Table 2'[1] ), DISTINCTCOUNT ( 'Table 2'[1] ), 0 )
VAR number =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Column]
                IN select1
                    && 'Table'[Material] = MAX ( 'Table'[Material] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        selectnumber = 0, 1,
        selectnumber = 1, IF ( MAX ( 'Table'[Column] ) IN select1, 1, 0 ),
        selectnumber >= 2, IF ( number = selectnumber, 1, 0 ),
        0
    )

The other steps remain the same.

2.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vigneshkrish333]

Hi @Anonymous . Thanks for the efforts, But I think you misunderstood my requirment. Let me explain my requirement in detail. Let's understand that with an example. As an example I want to find (filter) a 'Hexagonal Bolt' with some specifications and let's say we have 1000 hexagonal bolts avl with different specifications. Here specfications are nothing but Attributes (i.e THREAD SIZE {M10, M12,M16}, STANDARD {ISO 4014, ISO 4017}, FINISH {A2K, A2J, NO SURFACE TREATMENT}. 

And the "Standard Parts" table has all the hexagonal bolts with all it attributes (Specifications) and attributes values filled, meaning one item (Ex material numer 1234 ) will have THREAD SIZE as M10 STANDARD as ISO 4014 and FINISH as A2K and other item (Ex material numer 5678) will have THREAD SIZE as M12 STANDARD as ISO 4017 and FINISH as A2J and other item will (Ex material numer 91011) have THREAD SIZE as M64 STANDARD as ISO 4014 and FINISH as A3F.

Now I want to filter a bolt as per my requirement which matches the attribute values which I select; Example I want a bolt of STANDARD ISO 4014 and FINISH A3F. So now if I am selecting these attributes and attributes values from my Power Bi screen; currently it shows all the bolts which either has STANDARD as ISO 4014 OR FINISH as A3F; From our above example it lists two material numbers (i.e 1234, 91011 both, because 1234 has ISO 4014 as standard and 91011 has ISO 4014 as standard.

But my requirement is it should show the item (bolts) which has both the conditions satisfied, that is an bolt which has STANDARD as ISO 4014 AND FINISH as A3F; In our example material number 91011 only should be filtered.

How to achive this whith the same Slicers which I have given in the snap by means of some method????

[Anonymous]

HI,@Vigneshkrish333 

     Has the problem been solved? If it does, share your solution and accept it as a solution that will help other community members who have the same problem as you.

I think I did not explain to you clearly, the slicer will only deal with whether the column selected by the slicer contains the content you selected. Secondly, because there is interaction between visual objects, the slicer will interact with each other. However, your requirements, because they need to be related to whether the selection order of the slicer can be obtained, currently, DAX cannot be implemented. So that's why I gave you an alternative earlier.

Of course, you are welcome to share your new ideas with us.

Best Regards,

Leroy Lu

[Anonymous]

Hi,@Vigneshkrish333 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated column references:

Column = 'Table'[UNSPC Attributes]&" "&'Table'[UNSPSC Attributes Values]

3.Create calculated table references:

Table 2 = SELECTCOLUMNS('Table',"1",'Table'[Column])

4. Below are the measure I've created for your needs:

Measure = 
VAR select1=VALUES('Table 2'[1])
VAR number=IF(NOT(ISFILTERED('Table 2'[1])),2,CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),'Table'[Column] IN select1&&'Table'[Material]=MAX('Table'[Material]))))
RETURN IF(number=2,1,0)

5.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vigneshkrish333]

Hi @Anonymous I have created the calculated column (column), calculated table (table 2) and measure (measure)  as you told. But where to use the measure?

[Anonymous]

Hi,@Vigneshkrish333 

Regarding the issue you raised, my solution is as follows:
1.First you need to select the visual object:

2.Next add the measure to the visual object:

3.Then modify the filter:

4.Finally remove the measure from the visual object:

5.Here are the final results:

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vigneshkrish333]

Hi @Anonymous! Thanks for the efforts and reply. But still my requirement is not solved. My requirement is Just need to filter the items based on conditions in the slicers. To do that I have a first slicer (Hierarchy Slicer) which will bring the all items based on what is selected in that hierarchy slicer. in this case (Hexagonal Bolts), let's say, it filters out 1000 items (bolts) and shows in the table with all 1000 item's all 7 attributes (FINISH, GRADE, LENGTH, STANDARD, THREAD PITCh, THREAD SERIES, THREAD SIZE). (Refer below image)

Now the second slicer comes up, where I want to filter from this 1000 bolts that has "FINISH" as 'NO SURFACE TREATMENT and STANDARD as "ISO 4014" - i.e an item which has attributes values as these two. 

But currently if I select these two attributes as multiple selection (FINISH, STANDARD) and values from the third slicer (Attribute values slicer), the table appears with all bolts which either has NO SURFACE TREATMENT "OR" ISO 4014 as STANDARD. (Refer iamge)

But I want to filter the item which has both these values as it attribute values from these slicers itself.  any suggestion? if any clarity required please post as reply.

[Anonymous]

Hi,@Vigneshkrish333 

Thank you for your quick reply.

What we need to explain to you is that the purpose of the slicer is to check whether the value you selected exists in the column you set, and then filter out the entire row, and because of the interaction of visual objects, one of your slicer will affect the other slicer. As your test results show, it will only filter the UNSPSC Attributes Values column to "ISO 4014" or "NO SURFACE TREATMENT", and we don't have a dax function to get the order of your slicer selection. That's why I presented the above solutions to you earlier.

Of course, if you still want to keep the slicer style, here is another solution:

1.Create calculated column references:

Table 3 = SELECTCOLUMNS('Table',"1",'Table'[UNSPC Attributes],"1.1",'Table'[UNSPSC Attributes Values])

Table 4 = SELECTCOLUMNS('Table',"2",'Table'[UNSPC Attributes],"2.1",'Table'[UNSPSC Attributes Values])

And use all four columns as slicers.

2. Below are the measure I've created for your needs:

Measure 2 = 
VAR one=SELECTEDVALUE('Table 3'[1])
VAR one_1=SELECTEDVALUE('Table 3'[1.1])
VAR two=SELECTEDVALUE('Table 4'[2])
VAR two_1=SELECTEDVALUE('Table 4'[2.1])
RETURN IF(
CALCULATE(COUNTROWS('Table'),
FILTER(ALLSELECTED('Table'),
(
    (MAX('Table'[UNSPC Attributes])=one&&MAX('Table'[UNSPSC Attributes Values])=one_1)
||(MAX('Table'[UNSPC Attributes])=two&&MAX('Table'[UNSPSC Attributes Values])=two_1)
)
&&
'Table'[Material]=MAX('Table'[Material])
)
)=2,1,0)

3.Modify the filter as follows:

4.Here's my final result, which I hope meets your requirements.

If you find other solutions, you are welcome to share them with us, and then we hope you will accept your solution as a solution, which will make it easier for community members who share your problems to find solutions faster.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.