topic Re: Dax formula to filter table in DAX Commands and Tips

Dax formula to filter table

Ughd — Wed, 12 Jul 2023 11:42:03 GMT

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.

Re: Dax formula to filter table

eliasayyy — Wed, 12 Jul 2023 11:53:04 GMT

you need to show us the tables please

Re: Dax formula to filter table

Ughd — Wed, 12 Jul 2023 12:08:48 GMT

Hi,

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

Re: Dax formula to filter table

eliasayyy — Wed, 12 Jul 2023 14:13:13 GMT

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

Re: Dax formula to filter table

Anonymous — Fri, 14 Jul 2023 09:01:42 GMT

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.

Re: Dax formula to filter table

Ughd — Mon, 17 Jul 2023 09:56:57 GMT

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.

Re: Dax formula to filter table

Anonymous — Tue, 18 Jul 2023 01:54:08 GMT

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.

Re: Dax formula to filter table

eliasayyy — Tue, 18 Jul 2023 06:20:11 GMT

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