Creating a slicer from multiple columns and making it work together with another slicer

[Anonymous]

Hi everyone,

For a couple of days I've tried creating a slicer from data in multiple columns. This kinda worked using this post as inspiration.

Now I have another problem... I'd love to make my new slicer dependent on another slicer: when I pick "20" in the Contracttype slicer, I'd love the "Knowledge_unique" slicer to only show possibilities associated with this particular type of contract.

PBI Report: 

Data: 

I feel like I've tried making this work in a thousand different ways.. My latest try was applying "Measure_knowledge" as a filter on the knowledge_unique slicer (value > 0) but that doesn't seem to work. For some reason I can't figure, some of the possibilities seem to disappear when doing this although being a positive "match".

I created a data set and PBI-report to simulate what I'm trying to achieve in my original report. Feel free to download both from this folder (secure folder on a server provided by Danish authorities). Access code: aJ39~g7D

Maybe it isn't even possible to do this? Or maybe I need to make the initial slicer from several columns in some other way for this to work... I'm really looking forward to reading your suggestions 🙂

Best regards,

Henrik

[DataInsights · Accepted solution]

Hello,

Create a calculated table with unique combinations of Knowledge 1, Knowledge 2, etc., and Contract Type. This calculated table will be the data source for both slicers. Don't establish any relationships with this table.

SupplierSlicer = 
DISTINCT (
    UNION (
        SUMMARIZE ( Supplier, Supplier[Knowledge 1], Supplier[Contract Type] ),
        SUMMARIZE ( Supplier, Supplier[Knowledge 2], Supplier[Contract Type] )
    )
)

Unfiltered:

Filter Contract Type = 20:

Filter Contract Type = 20, and Knowledge Unique = Cooking:

Note: There's no need to display Measure_Knowledge in the table/matrix; you can add a filter to the table/matrix "Measure_Knowledge = 1".

[DataInsights]

Measure_Knowledge = 
IF (
    MIN ( Supplier[Knowledge 1] ) IN VALUES (SupplierSlicer[Knowledge] )
        || MIN ( Supplier[Knowledge 2] ) IN VALUES ( SupplierSlicer[Knowledge] ),
    1,
    BLANK ()
)