Forum Discussion

Gazza21's avatar
Gazza21
Frequent Visitor
7 years ago
Solved

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 eq...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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

affan's avatar
affan
Icon for Solution Sage rankSolution Sage
7 years ago

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's avatar
Gazza21
Frequent Visitor
7 years ago

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

 

hopefully this screenshot helps:

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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

  • Gazza21's avatar
    Gazza21
    Frequent Visitor
    7 years ago

    Thanks, exactly what I needed