cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Filtering multiple columns by the same values

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:

ApplicationStrategy 2019Strategy 2020Strategy 2021Strategy 2022Strategy 2023
App 1BuyBuyHoldHoldHold
App 2BuyBuyBuyBuyBuy
App 3SellSellSellSellSell
App 4Decomm    
App 5BuyBuySellSellSell
App 6BuyBuyBuyBuyBuy

 

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:

 

ApplicationStrategy 2019Strategy 2020Strategy 2021Strategy 2022Strategy 2023
App 1BuyBuyHoldHoldHold
App 2BuyBuyBuyBuyBuy
App 5BuyBuySellSellSell
App 6BuyBuyBuyBuyBuy

 

Thanks in advance!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Filtering multiple columns by the same values

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] )
    )
)

 

5.PNG6.PNG

 

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
        )
    )

 

 

7.PNG8.PNG

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper II
Helper II

Re: Filtering multiple columns by the same values

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

Thanks a lot !

View solution in original post

7 REPLIES 7
Highlighted
Helper II
Helper II

Filtering multiple columns in the table by one slicer / filter

Hi, I got stuck...

I have a below data set:

 

Aplication NameStrategy 2019Strategy 2020Strategy 2021Strategy 2022Strategy 2023
App 1SellBuyBuyBuyBuy
App 2BuyBuyBuyBuyBuy
App 3 SellSellSellSellSell
App 4Decomm    
App 5SellSellSellSellSell
App 6BuyBuyBuyBuyBuy
App 7BuyBuyHoldHoldHold
App 8BuyBuyBuyBuyBuy
App 9BuyBuyBuyBuyBuy
App 10BuyBuyBuyBuyBuy
App 11N/ABuyBuyBuyBuy

 

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 NameStrategy 2019Strategy 2020Strategy 2021Strategy 2022Strategy 2023
App 1SellBuyBuyBuyBuy
App 2BuyBuyBuyBuyBuy
App 6BuyBuyBuyBuyBuy
App 7BuyBuyHoldHoldHold
App 8BuyBuyBuyBuyBuy
App 9BuyBuyBuyBuyBuy
App 10BuyBuyBuyBuyBuy
App 11N/ABuyBuyBuyBuy

 

Is it achievable ?

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

 

 

Thanks!

Highlighted
Super User II
Super User II

Re: Filtering multiple columns by the same values

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

Don't forget to give thumbs up and accept this as a solution if it helped you
Highlighted
Helper II
Helper II

Re: Filtering multiple columns by the same values

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:

ApplicationStrategy 2019Strategy 2020Strategy 2021Strategy 2022Strategy 2023
App 1BuyBuyHoldHoldHold

 

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.

Highlighted
Community Support
Community Support

Re: Filtering multiple columns by the same values

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] )
    )
)

 

5.PNG6.PNG

 

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
        )
    )

 

 

7.PNG8.PNG

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper II
Helper II

Re: Filtering multiple columns by the same values

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

Thanks a lot !

View solution in original post

Highlighted
Frequent Visitor

Re: Filtering multiple columns by the same values

@v-lid-msft  this was great info!

 

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

Highlighted
Community Support
Community Support

Re: Filtering multiple columns by the same values

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors