Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Ughd
New Member

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.

1 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 )

Animation15.gif

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.

View solution in original post

7 REPLIES 7
v-binbinyu-msft
Community Support
Community Support

Hi @Ughd ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1689325143499.png

Table2:

vbinbinyumsft_1-1689325160986.png

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

Animation13.gifPlease 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.

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.

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

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 )

Animation15.gif

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
Super User
Super User

you need to show us the tables please 

Hi,

 

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

IMG_20230712_173250~2.jpg

IMG_20230712_173353~2.jpg

IMG_20230712_173421~2.jpg

 

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors