Dynamic filtering/slicing if text contains

[Gazza21]

Fairly new to Power BI so forgive me if I am overlooking something obvious.

I have a set of data that is essentially bits of equipment out on loan to clients, when we go out to repair a bit of equipment its useful for our technicians to know what else the client has, so I want to be able to use the text slicer to search for an equipment number but have the table visual return all pieces of equipment that client current has on loan, not just the specific piece of equipment that was searched on in the text slicer.

(for reasons I won't get into it was decided the technician cannot search on client details directly)

Does that make sense?

[affan]

Hi @Gazza21

Please share some sample data or your PIBX file. 

As you have mentioned that you need to be able to filter based on the bits of equipment, so one possible issue can be that the same bit ID can be available at multiple customers. Or the bit ids which are being searched are always unique?

Regards,

Affan

[Gazza21]

The equipment number is unique (its called Asset number in the image below).

hopefully this screenshot helps:

[Gazza21]

Thanks, exactly what I needed

[v-jiascu-msft · Accepted solution]

Hi @Gazza21,

I'm afraid the Search visual doesn't help in your scenario. Because it will filter out the unmatched records. Please download the demo in the attachment, which is the solution for your scenario.

1. Create an independent table of Assets.

Assets =
FILTER ( VALUES ( Table1[Asset] ), ISBLANK ( [Asset] ) = FALSE () )

2. Create a measure.

Measure =
VAR clients =
    CALCULATETABLE (
        VALUES ( Table1[ClientNumber] ),
        ALL ( Table1[AD1], Table1[AD2], Table1[Asset] )
    )
RETURN
    CALCULATE (
        IF ( MIN ( Table1[ClientNumber] ) IN clients, 1, BLANK () ),
        ALLEXCEPT ( Table1, Table1[ClientNumber] )
    )

3. Create a Table visual and a Slicer.

Best Regards,
Dale