Forum Discussion

adeelnazir786's avatar
adeelnazir786
Helper I
6 years ago
Solved

Need help in Fuzzy Lookup

Hi Everyone,

Download Source PBIX
I'm new in power BI,
I have 4 tables 1 is actual text/data, 3 are lookup tables (single word, 2 words, 3 words lookup).
I want to filter data table based on lookup table selection as shown in below image. 
Here frequency is dummy, means number of occurance of lookup value in data.

 

Expected Output:

 

 

 

Thanks & Regards,
Adeel

Show and Tell
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi adeelnazir786 ,

     

    We can create a measure and use it in visual filter to meet your requirement:

     

    VisualControl = 
VAR Title =
    SELECTEDVALUE ( 'data'[title], BLANK () )
RETURN
    IF (
        IF (
            ISFILTERED ( 'Sheet1'[key] ),
            CALCULATE (
                COUNTROWS ( 'Sheet1' ),
                FILTER ( 'Sheet1', CONTAINSSTRING ( Title, [key] ) )
            ) > 0,
            TRUE ()
        )
            && IF (
                ISFILTERED ( 'Sheet2'[key] ),
                CALCULATE (
                    COUNTROWS ( 'Sheet2' ),
                    FILTER ( 'Sheet2', CONTAINSSTRING ( Title, [key] ) )
                ) > 0,
                TRUE ()
            )
            && IF (
                ISFILTERED ( 'Sheet3'[key] ),
                CALCULATE (
                    COUNTROWS ( 'Sheet3' ),
                    FILTER ( 'Sheet3', CONTAINSSTRING ( Title, [key] ) )
                ) > 0,
                TRUE ()
            ),
        1,
        -1
    )

     

     


    Best regards,

     

6 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi adeelnazir786 ,

     

    Sorry for that, We cannot understand your data model clearly, Could you please provide a mockup sample  based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive and share the link here.
     
    Please don't contain any Confidential Information or Real data in your reply.
     
    Best regards,

     

    • adeelnazir786's avatar
      adeelnazir786
      Helper I
      6 years ago

      v-lid-msft , I dont know what was happened,

      Actually post was editted by mobile, after that image and source file becomes raw text 🙂 . 

      Can you please lookinto my problem, thanks in advance.

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi adeelnazir786 ,

         

        We can create a measure and use it in visual filter to meet your requirement:

         

        VisualControl = 
VAR Title =
    SELECTEDVALUE ( 'data'[title], BLANK () )
RETURN
    IF (
        IF (
            ISFILTERED ( 'Sheet1'[key] ),
            CALCULATE (
                COUNTROWS ( 'Sheet1' ),
                FILTER ( 'Sheet1', CONTAINSSTRING ( Title, [key] ) )
            ) > 0,
            TRUE ()
        )
            && IF (
                ISFILTERED ( 'Sheet2'[key] ),
                CALCULATE (
                    COUNTROWS ( 'Sheet2' ),
                    FILTER ( 'Sheet2', CONTAINSSTRING ( Title, [key] ) )
                ) > 0,
                TRUE ()
            )
            && IF (
                ISFILTERED ( 'Sheet3'[key] ),
                CALCULATE (
                    COUNTROWS ( 'Sheet3' ),
                    FILTER ( 'Sheet3', CONTAINSSTRING ( Title, [key] ) )
                ) > 0,
                TRUE ()
            ),
        1,
        -1
    )

         

         


        Best regards,