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 lo...
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,

     

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,

 

  • adeelnazir786's avatar
    adeelnazir786
    Helper I
    6 years ago

    v-lid-msft 

    Great work! Please share dax to calculate the frequency (Existance count) of selected text (lookup text).

    • adeelnazir786's avatar
      adeelnazir786
      Helper I
      6 years ago

      v-lid-msft ,

       

      I've been use part of your formula which returns the number of findings, that really working 😛 

      CALCULATE (
                COUNTROWS ( Sheet1 ),
                FILTER ( Sheet1, CONTAINSSTRING ( Title, [key] ) )
            )

       Thanks & Regards,

      Adeel

       

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

        Hi adeelnazir786 ,

         

        Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution, we believe it can benefit more users. If you have any other questions about this scenario, please kindly ask here and we will try to resolve it.


        Best regards,