Row filter at person level

[Arial12]

Hi,

my data looks like this:

vendor id         Product color

12345               Red

12345               White

12345              Yellow

23456               Red

23456              Green

34567               Red

34567                Silver

45678                Ross gold

 and the list goes on.

basically what I want is if any vendor whose has order a product in red,blue (in a real data they are over 50 colors to filter) then finter out those vendors and keeping a display:

45678     Ross gold 

i did try a page level filter but then it only takes a vendor record out with color Red and keeps the rest and I want to filter all all the records associated with that vendor, if a vendor has ever order a color I want to filter out.

Thanks for any suggestions and help!

[v-shex-msft]

Hi @Arial12,

You can refer to below steps to filter visual by previous filter result.

Steps:

1. Use color column to create new table as selector.

Color = VALUES('sample'[Product color]) 

2. Write measure to compare current item with select item, return flag based on check result.

Flag =
VAR current_id =
    LASTNONBLANK ( 'sample'[vendor id], [vendor id] )
VAR filtered =
    CALCULATETABLE (
        VALUES ( 'sample'[vendor id] ),
        'sample'[Product color] IN ALLSELECTED ( Color[Product color] )
    )
RETURN
    IF ( NOT ( current_id IN filtered ), "Y", "N" )

3. Create table visual(original table) and slicer(color selector table).

4. Drag measure to table visual visual level filter, switch mode to 'is', filter flag 'Y'.

Result:

Regards,

Xiaoxin Sheng

[Arial12]

Thank you so much Xiaoxin Sheng but I am connectin in DirectQuery Mode.

so, Lastnonblank doesn't work there. any other idea?

Thanks,

[v-shex-msft]

Hi @Arial12,

Current I also not have any effective methods to achieve this on 'direct query' mode, perhaps you can submit an idea for this requirement.

Regards,

Xiaoxin Sheng

[Arial12]

I was able to accomplish this without lookup function. i will post my solution shortly.