Use slicer from disconnected table to filter charts

[tableauspoiled]

Hi,
Scratching my head here.  Can't post actual data, so I've tried to simulate it.

I have a list of prospects ('Buyers'[Name]), each interested in one of my products ('Buyers'[Product]), "A", "B", or "C".

Each product is sold in multiple different colors.  When data is imported, each color option comes in on a different column as a true/false field.

Unfortunately (for me) a prospect may be interested in or buy multiple colors of the same product.

I have charts for both of these measures, all of which can be filtered by other fields via slicer, but need the option to filter them by color:

Leads (COUNT(Buyers[Name])

Deals = CALCULATE(COUNT(Buyers[Name]), Buyers[Purchased] = TRUE())

Because more than one color may be true at a time for each prospect, I can't just create one column on the original table to use in my slicer.  I am trying to build a slicer that filters off of a separate table with a list of those color names in a single column.  Selecting "Blue" would then include everything from the Buyer table, whether or not there was another color they were interested in.  And not selecting a color at all in the slicer would just return the overall results.

Unfortunately, I cannot seem to work out a filter or if statement that makes this work.  There's nothing to connect the tables on.  I have managed to get a var that will return the color value selected in the slicer, but can't figure out how to pass that along to a measure in the original table.

Slicer test =

var Color =

    SELECTEDVALUE(Slicer[Color])

return

   color

Side note (because there's never an easy solution in my life, lol), if this is even doable, will I still be able to filter the charts using the other slicers from the original table at the same time?  

Can anyone help?  Please?

[Ashish_Mathur]

Hi,

In the Query Editor, you should select all columns other then the Color columns, right click and then select "Unpivot Other Columns".  Revise your measures to:

Leads = DISTINCTCOUNT(Buyers[Name])

Deals = CALCULATE([Leads],Buyers[Purchased] = TRUE())

Does this work?

[tableauspoiled]

Hi Ashish,

Thanks for your reply!  It does, perfectly! 🎉🎉 Unfortunately the volume of data in this report makes it less than ideal, as it swells an already absurdly bloated table (~100k rows after I've filtered everything possible out of it).  😞  I was hoping there was a way to query the rows without it, but I'm not sure that's possible.

I did create a work around of sorts, where instead of a traditional slicer with all the values from a column I created individual list slicers for each column (each with only one color as a checkbox), then formatted them as a list in a single box as though it was a multi-select slicer.  The problem is that there will soon be additional "color columns", and that will be almost as awkward as the alternative.

[Ashish_Mathur]

Hi,

You are welcome.  I am not aware of a better method to solve this question.

[lbendlin]

Create a measure to probe the slicer. Use that measure as a visual level filter.

[tableauspoiled]

Thank you for the reply.  Could you explain that a little more?  I'm not sure what you mean by out of context, but I am having trouble figuring out what that type of measure would look like.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[tableauspoiled]

Hi lbendlin,

Buyers = {

("Bobby", "A", FALSE(), TRUE(), TRUE(),TRUE()),

("Suzy", "B", TRUE(), TRUE(), FALSE(), TRUE()),

("Fred", "B", FALSE(), FALSE(), TRUE(), FALSE()),

("John", "C", TRUE(), TRUE(), TRUE(), TRUE()),

("Liz", "A", TRUE(), FALSE(), TRUE(),TRUE()),

("Frank", "B", TRUE(), FALSE(), FALSE(), FALSE())}

The desired outcome is a single slicer like the "Colors" one below that filters the two charts.  But the colors in my goal come from separate columns instead of a single column like you would normally use.  I like the idea of measures that could query each column, but I can't figure out how to do it in a slicer format.