Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Arial12
Frequent Visitor

Row filter at person level

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!

 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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:

1.gif

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

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

 

Thanks,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.