Disconnected Slicer to slice a table

[pmay]

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

[rohit_singh · Accepted solution]

Hi @pmay ,

Please try this, provided your disconnected slicer looks like 

1) Create a measure that captures the selected value from your disconnected slicer

SelectedChain = 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 column

SelectFlag =

var _sel = dim_slicer[SelectedChain]

var _chain = MAX(DisconnectedSlicer[Value Chain])

RETURN

IF(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! 😊

[AFL]

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 

[AFL]

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.

[pmay]

Thanks - 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?

[pmay]

Yup this works.

So my error was misunderstanding this part:

 SelectedChain = SELECTEDVALUE(dim_slicer[Value])

I was putting the same disconnected slicer in there, rather than the field to search through.  Thank you very much!

In my initial post, I linked to another user who had asked the same question, you can probably just give them the same answer.

[rohit_singh]

Hppy to hear that it's working for you @pmay . I will put a link to this solution in the other post as well.

[pmay]

I've no idea why I misread that last time.  I even wondered why it would be a calculated column.
I've added the SelectFlag to my table visual, but every row still returns a 1.
My SelectedChain measure:

SelectedChain = SELECTEDVALUE('DIM Value Chains'[Value Chain])

Which is what's in the slicer, and the content of the slicer does look like you specified - just a single value for each value chain in my dataset.

Finally, my SelectFlag measure:

SelectFlag =

VAR _sel = [SelectedChain]

VAR _chain = MAX('DIM Value Chains'[Value Chain])

RETURN

IF(CONTAINSSTRING(_chain, _sel), 1,0)

When putting SelectedChain on a card, it returns the value selected in the Slicer.

[pmay]

Think I've just spotted my error this time - give me a moment 

[NickolajJessen]

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

[amitchandak]

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