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...
@Dax help
Custom Filter
filter non aggregate data using dax
power bi
  • 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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Ughd ,

Please try below steps:

1. below is my test table

Table:

Table2:

2. create a measure with below dax formula

Measure =
VAR a =
    SELECTEDVALUE ( 'Table 2'[Country] )
VAR b =
    CALCULATE (
        MAX ( 'Table 2'[Is European Union Country] ),
        'Table 2'[Country] = a
    )
VAR cur_country =
    SELECTEDVALUE ( 'Table'[Country] )
VAR val =
    SWITCH (
        TRUE (),
        b = "Yes",
            IF ( cur_country = a || cur_country = "Europen Union", 1 ),
        b = "No", IF ( cur_country = a, 1 )
    )
RETURN
    IF ( NOT ( HASONEVALUE ( 'Table 2'[Country] ) ), 1, val )

3. add a table visual with Table fields, add a slicer with Table2 field, add measure to table visual filter pane and set

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.

Ughd's avatar
Ughd
New Member
3 years ago

Hi v-binbinyu-msft,

 

It's working perfectly but users also want option to select multiple countries. Is it possible with this calculation?

 

Thanks in advance.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    if you see his video then you can see he seelected multiple countries so it should work