And Operation on Slicer Selection

MK3010 · January 26, 2021, 6:18am

Hi All,

Here is a scenario where we need to filter the table based on slicer selection. The default behavior of slicer selection is OR operation but what I am trying to achieve is AND operation which is different than what has been explained by SQLBI https://www.youtube.com/watch?v=tONOosN1xAs&feature=youtu.be , have tried different ways to accomplish this but could not. The only table present is Main table though I have tried creating a another table with single value column.

“Main” table

Test Table…

Present …
Once you select more than 1 slicer data it filters using OR condition. i.e if I select A and B then it filters for the below column which contains A or B

What I am trying to achieve is it should reflect A and B i. the value which has both in the Data column.

AND operation for slicer.pbix (26.1 KB)

Heather · January 27, 2021, 12:45pm

the only solution I can think of is to use two slicers, but this requires you to add additional information to your Test table:

eDNA - AND operation for slicer.pbix (445.8 KB)

MK3010 · January 27, 2021, 1:13pm

Hi @Heather,

Yes I would’ve used this method but we don’t restrict the user to select only 2. It needs to filter for all the selections. The actual data contains many value. And we can’t create so many slicers and ask user to keep selecting one value from each slicer.

Melissa · January 27, 2021, 4:20pm

Hi @MK3010,

See if this meets your requirement, if you don’t want this measure in your table (as img left) just place it in the filter pane as a visual level filter (result img on the right).

I hope this is helpful.

AntrikshSharma · January 27, 2021, 7:51pm

@MK3010 Try this:

AND operation for slicer.pbix (27.2 KB)

Mukesh =
VAR CurrentSelection =
    VALUES ( Test[Data List] )
VAR RelatedData =
    GENERATE (
        CurrentSelection,
        CALCULATETABLE ( Main, VALUES ( Test[Id] ), REMOVEFILTERS ( Main ) )
    )
VAR AddOccurence =
    ADDCOLUMNS (
        RelatedData,
        "Occurrence",
            VAR CurrentID = Main[Id]
            VAR CountOccurences =
                COUNTROWS ( FILTER ( RelatedData, [Id] = CurrentID ) )
            RETURN
                CountOccurences
    )
VAR SelectionCount =
    COUNTROWS ( ALLSELECTED ( Test[Data List] ) )
VAR FewColumns =
    SELECTCOLUMNS (
        AddOccurence,
        "Id", [Id],
        "Data", [Data],
        "Occurrence", [Occurrence]
    )
VAR SingleSelection =
    CONCATENATEX (
        FILTER (
            DISTINCT ( FILTER ( FewColumns, [Occurrence] = 1 ) ),
            [Id] IN VALUES ( Main[Id] )
        ),
        [Data],
        ", "
    )
VAR MultipleSelection =
    CONCATENATEX (
        FILTER (
            DISTINCT ( FILTER ( FewColumns, [Occurrence] >= 2 ) ),
            [Id] IN VALUES ( Main[Id] )
        ),
        [Data],
        ", "
    )
VAR Result =
    IF (
        ISINSCOPE ( Main[Id] ),
        IF ( SelectionCount = 1, SingleSelection, MultipleSelection )
    )
RETURN
    Result

MK3010 · January 28, 2021, 1:50am

Hi Antriksh,

Yes partially but it was only working for 2 selection, but my requirement is n selection as below… my actual data has many values in some cell it is more than 20.

Melissa’s code worked for me and the above screen shot is after applying her solution…
But would really look forward to understand your code( if you have little time to expand on that) after I finish your recent blog about Context transition. I would really recommend for other users to read his awesome blog…

Thanks
Mukesh

MK3010 · January 28, 2021, 2:02am

Hi @Melissa,

Thank you for your simple yet very powerful solution and it is working as per requirement, with little modification when there was no selection it was showing empty table.

FilterMeasure = 
VAR rTest =
    COUNTROWS ( ALLSELECTED ( Test[Data List] ) )
VAR mySelection =
    VALUES ( Test[Data List] )
RETURN
    IF (
        OR ( COUNTROWS ( mySelection ) = rTest, NOT ( ISFILTERED ( Test[Data List] ) ) ),
        1
    )

Thaks
Mukesh

AntrikshSharma · January 28, 2021, 7:00am

@MK3010 Is this the desired result:

AND operation for slicer.pbix (29.3 KB)

Heather · January 28, 2021, 11:47am

some great ideas here @AntrikshSharma & @Melissa !

MK3010 · January 28, 2021, 2:53pm

Hi @AntrikshSharma,

Saw your DAX and it’s really amazing to see how you have modified the output based on slicer selection and really a great way to think what we can achieve with DAX.

My expectation were as follow.
Once user select any number of value from the slicer the table should filter for those values present on the single cell i.e

If select E then should show all the rows which has E
If user select more than 1 value then table should show which has minimum selected value i.e if user select A and E then it should show all the rows which has A and E in it as below.
If user select suppose 3 values then it should show data which has minimum those value…
And it goes on …
And below combination it shows no data

Hope the above explains what i was looking for but i really got so many different answers to have in the bucket

Thanks
Mukesh

AntrikshSharma · January 28, 2021, 4:50pm

@MK3010 Looks like now it works as expected.
AND operation for slicer.pbix (32.2 KB)

Mukesh 5 = 
VAR SlicerSelection =
    ALLSELECTED ( Test[Data List] )
VAR MainTableAsPerCurrentSelection =
    CALCULATETABLE (
        Main,
        TREATAS ( CALCULATETABLE ( VALUES ( Test[Id] ), SlicerSelection ), Main[Id] ),
        REMOVEFILTERS ( Main )
    )
VAR RelatedData =
    GENERATE (
        MainTableAsPerCurrentSelection,
        CALCULATETABLE ( VALUES ( Test[Data List] ), SlicerSelection )
    )
VAR SlicerSelectionIncluded =
    FILTER (
        RelatedData,
        NOT ISEMPTY ( FILTER ( SlicerSelection, CONTAINSSTRING ( [Data], [Data List] ) ) )
    )
VAR AddOccurence =
    ADDCOLUMNS (
        SlicerSelectionIncluded,
        "Occurrence",
            VAR CurrentID = Main[Id]
            VAR CountOccurences =
                COUNTROWS ( FILTER ( RelatedData, Main[Id] = CurrentID ) )
            RETURN
                CountOccurences
    )
VAR SelectionCount =
    COUNTROWS ( SlicerSelection )
VAR ReduceColumns =
    SELECTCOLUMNS (
        AddOccurence,
        "Id", Main[Id],
        "Data", Main[Data],
        "Occurrence", [Occurrence]
    )
VAR StringToReturn =
    CONCATENATEX (
        FILTER (
            DISTINCT ( FILTER ( ReduceColumns, [Occurrence] = SelectionCount ) ),
            [Id] IN VALUES ( Test[Id] )
        ),
        [Data],
        ", "
    )
VAR Result =
    IF ( ISINSCOPE ( Test[Id] ), StringToReturn )
RETURN
    Result

MK3010 · January 29, 2021, 1:23am

Hi @AntrikshSharma,

Yes it working with one exception i.e if nothing is selected it should display all the values but it is showing blank
Current :-

Expected:-

Thanks
Mukesh

AntrikshSharma · January 29, 2021, 7:53am

@MK3010 This will work:

Mukesh 7 = 
IF (
    ISINSCOPE ( Test[Id] ),
    VAR TestTableWithMain =
        ADDCOLUMNS ( Test, "Main[Data]", RELATED ( Main[Data] ) )
    RETURN
        IF (
            ISFILTERED ( Test[Data List] ),
            VAR SlicerSelection =
                ALLSELECTED ( Test[Data List] )
            VAR SelectionCount =
                COUNTROWS ( SlicerSelection )
            VAR SelectionMade =
                VAR SlicerSelectionIncluded =
                    FILTER (
                        TestTableWithMain,
                        NOT ISEMPTY (
                            FILTER ( SlicerSelection, CONTAINSSTRING ( Main[Data], Test[Data List] ) )
                        )
                    )
                VAR AddOccurence =
                    ADDCOLUMNS (
                        SlicerSelectionIncluded,
                        "Occurrence",
                            VAR CurrentID = Test[Id]
                            VAR CountOccurences =
                                COUNTROWS ( FILTER ( SlicerSelectionIncluded, Test[Id] = CurrentID ) )
                            RETURN
                                CountOccurences
                    )
                VAR ReduceColumns =
                    SELECTCOLUMNS ( AddOccurence, "Data", Main[Data], "Occurrence", [Occurrence] )
                VAR StringToReturn =
                    CONCATENATEX (
                        DISTINCT ( FILTER ( ReduceColumns, [Occurrence] = SelectionCount ) ),
                        [Data],
                        ", "
                    )
                RETURN
                    StringToReturn
            RETURN
                SelectionMade,
            CONCATENATEX (
                DISTINCT (
                    SELECTCOLUMNS ( TestTableWithMain, "Data", Main[Data] )
                ),
                [Data],
                ", "
            )
        )
)

MK3010 · January 29, 2021, 9:11am

Hi @AntrikshSharma,

Yes it is working as intended. And using this I don’t have to add filter.

The code goes so deep if you can explain a little then it will be great.

Thanks
Mukesh

AntrikshSharma · February 1, 2021, 3:33pm

@MK3010

Here is the commented DaxStudio file Mukesh.dax (3.6 KB)

Regarding the Occurence part, I noticed a pattern so what I did is basically count how many time all of the IDs are repeating and for the final string only return values where Occurrence is equal to the number of values selected in the slicer

MK3010 · February 1, 2021, 4:14pm

Hi @AntrikshSharma,

Thanks a lot for this, much appreciated.

Thanks
Mukesh