Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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,
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!
@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.
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.
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,
@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,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |