Filtering multiple columns by the same values

[Kopek]

Hi Everyone,

I am struglling with one thing and not able to find out any solution.

I have a data , which are a simple column, example below:

Application	Strategy 2019	Strategy 2020	Strategy 2021	Strategy 2022	Strategy 2023
App 1	Buy	Buy	Hold	Hold	Hold
App 2	Buy	Buy	Buy	Buy	Buy
App 3	Sell	Sell	Sell	Sell	Sell
App 4	Decomm
App 5	Buy	Buy	Sell	Sell	Sell
App 6	Buy	Buy	Buy	Buy	Buy

And I would like to have one filter with Buy, Sell, Hold, Decomm, which will filter all 5 columns.

Eg, when filterred by BUY, the outcoume should be:

Application	Strategy 2019	Strategy 2020	Strategy 2021	Strategy 2022	Strategy 2023
App 1	Buy	Buy	Hold	Hold	Hold
App 2	Buy	Buy	Buy	Buy	Buy
App 5	Buy	Buy	Sell	Sell	Sell
App 6	Buy	Buy	Buy	Buy	Buy

Thanks in advance!

[v-lid-msft · Accepted solution]

Hi @Kopek ,

First of all, we need create a calculated table with all the possible value, it does not need to have relation with fact table.

FilterTable =
DISTINCT (
    UNION (
        DISTINCT ( 'Table'[Strategy 2019] ),
        DISTINCT ( 'Table'[Strategy 2020] ),
        DISTINCT ( 'Table'[Strategy 2021] ),
        DISTINCT ( 'Table'[Strategy 2022] ),
        DISTINCT ( 'Table'[Strategy 2023] )
    )
)

。

Then we can create a measure and use it in the visual filter

IsContain = 
VAR t =
    FILTERS ( 'FilterTable'[Strategy 2019] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Strategy 2019] IN t
                || 'Table'[Strategy 2020] IN t
                || 'Table'[Strategy 2021] IN t
                || 'Table'[Strategy 2022] IN t
                || 'Table'[Strategy 2023] IN t
        )
    )

Best regards,

[Kopek]

Hi, I got stuck...

I have a below data set:

Aplication Name	Strategy 2019	Strategy 2020	Strategy 2021	Strategy 2022	Strategy 2023
App 1	Sell	Buy	Buy	Buy	Buy
App 2	Buy	Buy	Buy	Buy	Buy
App 3	Sell	Sell	Sell	Sell	Sell
App 4	Decomm
App 5	Sell	Sell	Sell	Sell	Sell
App 6	Buy	Buy	Buy	Buy	Buy
App 7	Buy	Buy	Hold	Hold	Hold
App 8	Buy	Buy	Buy	Buy	Buy
App 9	Buy	Buy	Buy	Buy	Buy
App 10	Buy	Buy	Buy	Buy	Buy
App 11	N/A	Buy	Buy	Buy	Buy

And I would like to set up a  one Buy/ Hold/ Sell/ Decomm/ N/A Filter  which will filter all 5 Strategy columns.

e.g. When filter by BUY i would have a below outcome:

Aplication Name	Strategy 2019	Strategy 2020	Strategy 2021	Strategy 2022	Strategy 2023
App 1	Sell	Buy	Buy	Buy	Buy
App 2	Buy	Buy	Buy	Buy	Buy
App 6	Buy	Buy	Buy	Buy	Buy
App 7	Buy	Buy	Hold	Hold	Hold
App 8	Buy	Buy	Buy	Buy	Buy
App 9	Buy	Buy	Buy	Buy	Buy
App 10	Buy	Buy	Buy	Buy	Buy
App 11	N/A	Buy	Buy	Buy	Buy

Is it achievable ?

I thought it's fairly easy, but apparently it is not...

Thanks!

[Tahreem24]

@Kopek ,

Please take Slicer from Visual Pane and add "Strategy 2019" field in that slicer.

Please Give KUDOS to this post and Accept this as a solution so other member can take help from it.

[Kopek]

Hi ,
Hi , it will not work for me unfortunately.

That slicer you proposed will filter the entire table only based on the values from the Strategy 2019 coumn.

So if i filter by HOLD, my table will be empty.

Whereas when filterring by hold i would like to see below outcome:

Application	Strategy 2019	Strategy 2020	Strategy 2021	Strategy 2022	Strategy 2023
App 1	Buy	Buy	Hold	Hold	Hold

I would like to create kind of universal filter which will filter the entire table.
So if filterred by hold i would like to see all rows containing HOLD value, when filterred by BUY i would like to see all rows containing BUY value...

Hope it makes sense..

I tried to create a mapping tab, but i relalized I can have only one relationship beetwen 2 tables, so it does not work either.

[#6]

Application	Strategy 2019	Strategy 2020	Strategy 2021	Strategy 2022	Strategy 2023
App 1	Buy	Buy	Hold	Hold	Hold

[v-lid-msft · Accepted solution]

Hi @Kopek ,

First of all, we need create a calculated table with all the possible value, it does not need to have relation with fact table.

FilterTable =
DISTINCT (
    UNION (
        DISTINCT ( 'Table'[Strategy 2019] ),
        DISTINCT ( 'Table'[Strategy 2020] ),
        DISTINCT ( 'Table'[Strategy 2021] ),
        DISTINCT ( 'Table'[Strategy 2022] ),
        DISTINCT ( 'Table'[Strategy 2023] )
    )
)

。

Then we can create a measure and use it in the visual filter

IsContain = 
VAR t =
    FILTERS ( 'FilterTable'[Strategy 2019] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Strategy 2019] IN t
                || 'Table'[Strategy 2020] IN t
                || 'Table'[Strategy 2021] IN t
                || 'Table'[Strategy 2022] IN t
                || 'Table'[Strategy 2023] IN t
        )
    )

Best regards,

[kararandle2710]

@v-lid-msft  this was great info!

I'm curious as to why you use COUNTROWS in the calculated Meassure?

[v-lid-msft]

Hi @kararandle2710 ,

We can use COUNTROWS to caluculate is there any rows in filtered table that match the condition, we can also use && to test all the condition and return 1 or 0.

Best regards,

[Kopek · Accepted solution]

Hi @v-lid-msft  that works perfectly fine!

Thanks a lot !