Forum Discussion

Begbie's avatar
Begbie
Helper I
4 years ago
Solved

Filter data in table based on silcer

Hi All,

 

I'm hoping someone can point me in the right direction here, as my PBI isn't the best. 

 

Currently, I have a report that has a slicer in it, with options of:

 

Secure API

Secure Code

Secure Infrastructure

etc...

 

When a user choose one of those slicer options, it changes the data displayed on the table within the report, usual PBI stuff 😊 but what I'm trying to figure out is to filter the data in the table based on the slicer option choosen.

 

For example:

 

If they choose Secure API, I would like the table to only show results that have 'Devops' & 'CD' in it. 

If they choose Secure Code, I would like the table to only show results that have 'Devops', 'CD' & 'Classic' in it.

If they choose Secure Infrastructure, I would like the table to only show results that have 'Classic' in it.

etc..

 

I think it is possible, but seems my Google-Fo skills isn't quite pointing me in the right direction to help me get to what I'm trying to achieve.

 

Any helps or pointers are greatly appreciated.

 

Many thanks 👍

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Try creating a measure to use as a filter for the visual. You will need an independent table for the slicer selection

    Let the independent table values be DSlicer[Value], the fact table be FactTable and the selection column be FactTable[Selection])

     

    Filter measure =
SWITCH (
    SELECTEDVALUE ( DSlicer[Value] ),
    "Secure API",
        COUNTROWS (
            CALCULATETABLE ( FactTable, FactTable[Selection] IN { "Devops", "CD" } )
        ),
    "Secure Code",
        COUNTROWS (
            CALCULATETABLE (
                FactTable,
                FactTable[Selection] IN { "Devops", "CD", "Classic" }
            )
        ),
    "Secure Infrastructure",
        COUNTROWS (
            CALCULATETABLE ( FactTable, FactTable[Selection] IN { "Classic" } )
        )
)

     

    Select the visual and add this measure as a filter, setting the value to greater or equal to 1

  • Begbie's avatar
    Begbie
    4 years ago

    PaulDBrown 

     

    Just wanted to say thank you for the above and this seems to have resolved my issue 😀

     

    No idea how you guys do it and have all this knowledge, but the above solution with the measure helped achieve what I wanted, so a big thank you from me!!

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Begbie ,

     

    Define a dimension table (dimSlicer) like this:

     

    |  slicerValue  |  codeToInclude  |
-----------------------------------
|  Secure API   |  Devops         |
|  Secure API   |  CD             |
|  Secure Code  |  Devops         |
|  Secure Code  |  CD             |
|  Secure Code  |  Classic        |
|  ...

     

     

    Define a bridge table (codeBridge) that is just a unique list of codes to include:

     

    |  codeToInclude  |
-------------------
|  Devops         |
|  CD             |
|  Classic        |
|  ...

     

     

    In your data model, relate as follows:

     

    - dimSlicer[codeToInclude] MANY : ONE codeBridge[codeToInclude] (filter direction BOTH)

    - codeBridge[codeToInclude] ONE : MANY factTable[Code]

     

    Use dimSlicer[slicerValue] in your page slicer.

     

    Pete

    • Begbie's avatar
      Begbie
      Helper I
      4 years ago

      Hi Pete,

       

      Thank you for the pointers and that has seemed to help with the solution, but seems I now have double / triple entries in my table 😣
      This is what is being shown when I use the dimSlicer suggestion you gave above:

      Whereas before it would show:

       

      I'm sure it's probably down to something silly.

       

      Thanks.

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Try creating a measure to use as a filter for the visual. You will need an independent table for the slicer selection

        Let the independent table values be DSlicer[Value], the fact table be FactTable and the selection column be FactTable[Selection])

         

        Filter measure =
SWITCH (
    SELECTEDVALUE ( DSlicer[Value] ),
    "Secure API",
        COUNTROWS (
            CALCULATETABLE ( FactTable, FactTable[Selection] IN { "Devops", "CD" } )
        ),
    "Secure Code",
        COUNTROWS (
            CALCULATETABLE (
                FactTable,
                FactTable[Selection] IN { "Devops", "CD", "Classic" }
            )
        ),
    "Secure Infrastructure",
        COUNTROWS (
            CALCULATETABLE ( FactTable, FactTable[Selection] IN { "Classic" } )
        )
)

         

        Select the visual and add this measure as a filter, setting the value to greater or equal to 1