Dax formula to filter table

[Ughd]

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.

[Anonymous]

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]

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.

[eliasayyy]

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

[Anonymous · Accepted solution]

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]

you need to show us the tables please 

[Ughd]

Hi,

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

 

 

[eliasayyy]

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