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 Cod...
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!!

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

    • Begbie's avatar
      Begbie
      Helper I
      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!!

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Begbie ,

     

    Where did you get this column from?

    It appears to be what is causing the row duplication.

    I don't think this column should come from either of the tables that we added before, but rather from the [code] column in your fact table.

     

    Pete

    • Begbie's avatar
      Begbie
      Helper I
      4 years ago

      Hi BA_Pete 

       

      Thank you for coming back to me. Yes, it did seem I did choose the wrong column initially, but then I had other things happen where the data didn't filter quite correctly, but PaulDBrown solution seems to have helped me for what I needed to do.

       

      Thanks again 😊