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 from slicer, table should show selected country + EU. In case non-EU country is selected, table should only show selected country.

 

I have created a new table, not related to data model with list of countries and created below measure:

 

Filter=If(selectedvalue(fact_table[country])="European Union"||Selectedvalue(facr_table[country] in values(unrelated_table[country]),1,0)

 

I have applied this measure as visual filter to table visualisation and set it's value to 1.

 

It's partially working and showing European Union when I select an EU country or non-EU country from slicer.

 

Kindly help me to modify this calculation so it only shows European Union when an EU country is selected.

@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.

7 Replies

    • Ughd's avatar
      Ughd
      New Member
      3 years ago

      Hi,

       

      Below are the screen shots. Fact table is very simple with 4 fields, country, doc name, description, contact person.

       

       

       

      • eliasayyy's avatar
        eliasayyy
        Memorable Member
        3 years ago

        ok it looks like you are using 1 table since european country is under countries powerbi cant tell which country is inside the eu, you need to maybe add a new column that says which countries are part of the eu

         

  • 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.