Fuzzy queries by using DAX codes

[v-kongfanf-msft]

Scenario:
We often use the concept of fuzzy queries in our life or work. In Power BI Desktop, we can do the same thing with fuzzy queries. In this article, I will show you how to write fuzzy queries using DAX code.

Expected Result:

I have a slicer and a table in a report. I hope to realize the following results:

• When I select a letter, it filters the table to show all results that have that letter.

• When I select two or three letters, it filters results with all of these letters.

• I select more than three letters, I can also do fuzzy queries without adding DAX code.

Sample Data:
First I create two tables Table A and Table B, and I use Table A column in a table visual and Table B column in a slicer.

How:
Firstly I want to realize the expected result when selecting only one character. I create the following measure. It uses FIND function to identify whether [Column a] string contains the selected character from the slicer. Then add the measure to the table visual.

MEASURE =
VAR _Slicer =
    SELECTEDVALUE ( 'Table B'[Column b] )
RETURN
    IF (
        FIND ( _Slicer, SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () ) <> BLANK (),
        _Slicer
    )

However, the previous measure only works when selecting only one character. If we hope to filter by two or three characters, I think of another measure. Here we need to use the FIND function and MID function. FIND function is to return a text string in another text string in the starting position. MID function is to provide the starting position and length of the case, from the text string to return to the middle of the string.

Measure 2 =
VAR _slicer =
    CONCATENATEX ( 'Table B', 'Table B'[Column b] )
RETURN
    IF (
        COUNT ( 'Table B'[Column b] ) = 1
            && FIND ( MID ( _slicer, 1, 1 ), SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () )
                <> BLANK (),
        _slicer,
        IF (
            COUNT ( 'Table B'[Column b] ) = 2
                && FIND ( MID ( _slicer, 1, 1 ), SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () )
                    <> BLANK ()
                && FIND ( MID ( _slicer, 2, 1 ), SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () )
                    <> BLANK (),
            _slicer,
            IF (
                COUNT ( 'Table B'[Column b] ) = 3
                    && FIND ( MID ( _slicer, 1, 1 ), SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () )
                        <> BLANK ()
                    && FIND ( MID ( _slicer, 2, 1 ), SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () )
                        <> BLANK ()
                    && FIND ( MID ( _slicer, 3, 1 ), SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () )
                        <> BLANK (),
                _slicer
            )
        )
    )

Through the above DAX code we can indeed select three or fewer than three characters to filter the table. However it’s not very smart as if we hope to select more characters, it won’t work. And I don’t want to repeat many similar codes like

FIND ( MID ( _slicer, 1, 1 ), SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () )

to achieve the expected result. So I think if it can be optimized further. After some research, I finally get the following DAX code.

Measure_4 =
VAR _Slicer =
    VALUES ( 'Table B'[Column b] ) // This variable gets all the unique values of 'Column b' in 'Table B'. 
VAR _vtable =
    ADDCOLUMNS (
        GENERATE ( 'Table A', _Slicer ),
        "AAA",
            IF ( FIND ( [Column b], [Column a],, BLANK () ) <> BLANK (), [Column b] ) // Combine ‘Table A’ with _Slicer 
    )
VAR _vtable2 =
    SUMMARIZE (
        _vtable,
        [Column a],
        "BBB",
            IF (
                LEN ( CONCATENATEX ( _Slicer, [Column b] ) )
                    = LEN (
                        CONCATENATEX ( FILTER ( _vtable, [Column a] = EARLIER ( [Column a] ) ), [AAA] )
                    ),
                CONCATENATEX ( FILTER ( _vtable, [Column a] = EARLIER ( [Column a] ) ), [AAA] )
            )
    )
RETURN
    IF (
        CONCATENATEX ( FILTER ( _vtable2, [Column a] = 'Table A'[Column a] ), [BBB] )
            <> BLANK (),
        CONCATENATEX ( FILTER ( _vtable2, [Column a] = 'Table A'[Column a] ), [BBB] )
    )

This allows fuzzy queries no matter how many characters you select.

Summary:

As you can see from the above example, when we keep updating the DAX code, we can change from the original more clunky approach to the final dynamic yet concise approach. In addition, when we come to a practical scenario that we may want to filter by more strings in the slicer, the last DAX solution is applicable too. Below is a fuzzy query I did for a car brand, the exact steps can be found in my .pbix file for your own reference.

Author:Dragon Chou
Reviewer:Ula and Kerry

Sample.pbix