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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gazza21
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

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.

View solution in original post

4 REPLIES 4
affan
Solution Sage
Solution Sage

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

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

 

hopefully this screenshot helps:

 

asset image.JPG

Thanks, exactly what I needed

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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