Forum Discussion
Resolver IDisconnected Slicer to slice a table
So I have a table that contains some information about services, like this:
| Service | Value Chain | Service Level |
| ABC | Ops, HR | Bronze |
| DEF | IT, Ops | Gold |
| GHI | IT, HR | Silver |
| JKL | HR, Ops | Bronze |
| MNO | Ops, HR, IT | Gold |
I need to be able to filter it, for example, to show only Service that contains "HR" in Value Chain.
Now, I know your first instinct would be to split the columns, do some pivoting, creating multiple rows for each Service, with a single value chain per service, but elsewhere this changes my relationships into Many to Many, screws my averages and other measures, etc.
So, I decided I'd probably need to resort to a disconnected slicer, which has all the distinct, comma separated values in Value Chain (IT, Ops, HR). However, how do I make that slicer impact a Table to show only Services which, for instance, contain IT in the value chain?
This question was asked before, but not answered: Link
Hi pmay ,
Please try this, provided your disconnected slicer looks like
1) Create a measure that captures the selected value from your disconnected slicerSelectedChain = SELECTEDVALUE(dim_slicer[Value])2) On your main table, create a measure that acts as a flag to display 1 when the selected value is found within the columnSelectFlag =var _sel = dim_slicer[SelectedChain]var _chain = MAX(DisconnectedSlicer[Value Chain])RETURNIF(CONTAINSSTRING(_chain, _sel), 1 , 0)This will give you the following result. When you've selected "IT", all rows within column "Value Chain" that have value "IT" will be assigned a value of 1.In the final step, add measure "SelectFlag" as a visual level filter to the table and set value to 1. This will show you only those rows that have values "IT"
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
13 Replies
rohit_singhSolution Sage
Hi pmay ,
Please try this, provided your disconnected slicer looks like
1) Create a measure that captures the selected value from your disconnected slicerSelectedChain = SELECTEDVALUE(dim_slicer[Value])2) On your main table, create a measure that acts as a flag to display 1 when the selected value is found within the columnSelectFlag =var _sel = dim_slicer[SelectedChain]var _chain = MAX(DisconnectedSlicer[Value Chain])RETURNIF(CONTAINSSTRING(_chain, _sel), 1 , 0)This will give you the following result. When you've selected "IT", all rows within column "Value Chain" that have value "IT" will be assigned a value of 1.In the final step, add measure "SelectFlag" as a visual level filter to the table and set value to 1. This will show you only those rows that have values "IT"
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊
pmayThanks - I really thought this would work, but sadly not.
The column I added seems to never see the content of the slicer, it returns 1 on every single row, regardless of what I pick in the disconnected slicer
- rohit_singh
Hi pmay ,
Apologies. selectflag must be a measure and not a calculated column.
Also, could you please verify if you're capturing the slicer value correctly?
Good afternoon,
I have a followup question to the above, if I may. I have gotten the original solution working as intended, many thanks for that. I am trying to modify the query a bit and have gotten stuck for quite a while now.
I have a main table containing the columns (amongst others):
- [DATE]
- [CLIENT]
- [REP]
- [TICKET ID]
I have a disconnected slicer which allows for the selection of the [TICKET ID], which is unique. The goal is to filter the main table by only showing those entries that share the same [DATE], [CLIENT] and [REP] as the row with the selected [TICKET ID] -> so show ALL [TICKET ID]s of the given [DATE], [CLIENT] and [REP].
I have tried using LOOKUPVALUE to retrieve the corresponding values from the other columns and then setting flags for them as well like in the given solution, but I seem to be fundamentally misunderstanding things about the interaction of filters.
Any help would be greatly appreciated.
Regards
Andreas
Never mind, I have finally got it. I guess at some point I had accidentally reconnected the disconnected slicer to the main table, which caused all sorts of headaches.
- amitchandak
Super User
pmay , You can use containsstring or search, but when you select more than one value that will be a big challenge
example
countrows(filter(Table, search(selectedvalue(Value[Value]) , Table[Value],,0)>0))
- pmay
Thank you for taking the time to try to help.
I'm OK with limiting to Single Select (or All).
Your measure would return me a count of the rows, but I don't need a count, I want all the values in all the columns. There are actually a bunch more columns, but I guess the logic will be the same regardless (if it's possible at all).
I can make it work for a single value, easily enough:SLA 1 Month Span (SelectedValueOnly) = CALCULATE([SLA 1 Month Span], FILTER('Service Portfolio', CONTAINSSTRING('Service Portfolio'[Value Chain], SELECTEDVALUE('DIM Value Chains'[Value Chain]))))
NickolajJessenYou might be able to use the Custom Text filter from the marketplace
- pmay
This works, but relies on my users knwoing the correct value chain acronym. I'll have a think for a few hours to see if I can put that responsibility on them, to spell a few letters correctly.
Regardless, this is a great add-in to know about!
