Forum Discussion

Ughd's avatar
Ughd
New Member
3 years ago
Solved

Dax formula to filter table

Hi,   I have text data visualised in a table in Powe bi. European Union is also available in data as a country. Requirement is to custom filter table in a way that if user select any EU country fro...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Ughd ,

    Please replace before dax formula with below dax formula:

    Measure =
    VAR a =
        SELECTCOLUMNS ( 'Table 2', "CT", [Country] )
    VAR str =
        CONCATENATEX ( a, [CT] )
    VAR b =
        CALCULATETABLE (
            VALUES ( 'Table 2'[Is European Union Country] ),
            FILTER ( ALL ( 'Table 2' ), CONTAINSSTRING ( str, 'Table 2'[Country] ) )
        )
    VAR str2 =
        CONCATENATEX ( b, [Is European Union Country] )
    VAR cur_country =
        SELECTEDVALUE ( 'Table'[Country] )
    VAR val =
        SWITCH (
            TRUE (),
            CONTAINSSTRING ( str2, "Yes" ),
                IF ( CONTAINSSTRING ( str, cur_country ) || cur_country = "Europen Union", 1 ),
            NOT ( CONTAINSSTRING ( str2, "Yes" ) ), IF ( CONTAINSSTRING ( str, cur_country ), 1 )
        )
    RETURN
        IF ( NOT ( ISFILTERED ( 'Table 2'[Country] ) ), 1, val )
    

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.