Filter data in table based on silcer

[Begbie]

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 👍

[PaulDBrown · Accepted solution]

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

[BA_Pete]

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]

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.

[BA_Pete]

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]

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 😊

[PaulDBrown · Accepted solution]

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 · Accepted solution]

@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!!