calculation of a table from a table with selection

[ffiore]

hi, after many attempts of solving it, I post my request.

I have a table with 2 columns [SupplierID] and [ItemID]. In my dashboard, I would like the user to select one or more items (first item set) from a table visual, get the corresponding set of suppliers' names ("first supplier set"), find all the articles supplied by the suppliers in the "first supplier set" (to determine "a second item set"), and then finally find all the suppliers capable of suppling at least one item of the "second Item set". This is the iteration:

I hope it's clear enough.
many thanks for your help

ffiore

[ffiore]

it works like a charm!
thank you!!

[johnt75 · Accepted solution]

First you'd need to create a helper table with all the distinct values of Item ID. This shouldn't be related to any other tables, you would only use it in the slicer for the user's initial selection.

Then you can create a measure like

Supplier is visible =
VAR CurrentSupplier =
    SELECTEDVALUE ( 'Supplier'[Supplier ID] )
VAR SelectedItems =
    TREATAS ( VALUES ( 'Helper Items'[Item ID] ), 'Supplier'[Item ID] )
VAR FirstSupplierSet =
    CALCULATETABLE ( VALUES ( 'Supplier'[Supplier ID] ), SelectedItems )
VAR SecondItemSet =
    CALCULATETABLE (
        VALUES ( 'Supplier'[Item ID] ),
        REMOVEFILTERS ( 'Supplier' ),
        FirstSupplierSet
    )
VAR FinalSuppliers =
    CALCULATETABLE (
        VALUES ( 'Supplier'[Supplier ID] ),
        REMOVEFILTERS ( 'Supplier' ),
        SecondItemSet
    )
VAR Result =
    IF ( CurrentSupplier IN FinalSuppliers, 1, 0 )
RETURN
    Result

and use this as a visual level filter on a table with columns from the supplier table, set to only show when the value is 1.

[ffiore]

Hi, 

I would like the same, but
1. allowing selection of multiple values by the user (e.g. he clicks on 2 or more Item ID's), and
2. FirstSupplierSet has multiple values (e.g. Supplier1, Supplier32, Supplier54...)
this code dosesn't seem to handle this.
thank you

[johnt75]

It should work for multiple items as well, unless the requirements are different. The first supplier set would consist of suppliers who supply any of the selected items.