Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kopek
Helper IV
Helper IV

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

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

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

Thanks a lot !

View solution in original post

7 REPLIES 7
Kopek
Helper IV
Helper IV

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!

Tahreem24
Super User
Super User

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

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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.

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.

@v-lid-msft  this was great info!

 

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

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.

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

Thanks a lot !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.