cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gazza21 Frequent Visitor
Frequent Visitor

Dynamic filtering/slicing if text contains

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic filtering/slicing if text contains

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.

Dynamic-filteringslicing-if-text-contains

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Highlighted
affan Established Member
Established Member

Re: Dynamic filtering/slicing if text contains

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 Frequent Visitor
Frequent Visitor

Re: Dynamic filtering/slicing if text contains

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

 

hopefully this screenshot helps:

 

asset image.JPG

Community Support Team
Community Support Team

Re: Dynamic filtering/slicing if text contains

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.

Dynamic-filteringslicing-if-text-contains

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Gazza21 Frequent Visitor
Frequent Visitor

Re: Dynamic filtering/slicing if text contains

Thanks, exactly what I needed