topic Re: DAX measure to find unique values from two columns in DAX Commands and Tips

DAX measure to find unique values from two columns

Anonymous — Fri, 17 Mar 2023 06:42:35 GMT

Hi,

I have data for two columns,

However I would like to have the unique values from both the columns.

Ex- Country 1 and Country 2 details are as follows-

Country 1

Ghana

Netherlands

Canada

France

Germany

Hungary

Poland

Romania

United States

Switzerland

Portugal

Germany

Spain

Russian Federation

Vietnam

Singapore

Spain

Italy

Ukraine

Netherlands

United States

Poland

Uruguay

Malaysia

South Africa

India

Australia

Singapore

Brazil

Turkey

Argentina

Paraguay

United Kingdom

Mexico

Country 2

Netherlands

United States

Malaysia

Ghana

Canada

Switzerland

Spain

Uruguay

Portugal

Poland

Germany

Hungary

France

China

Singapore

Mexico

United States

Singapore

Italy

Canada

South Africa

Ukraine

Russian Federation

Finland

Switzerland

Romania

Bermuda

Brazil

Egypt

Turkey

India

Argentina

Netherlands

United Kingdom

France

Germany

Italy

Spain

Malaysia

China

Poland

Finland

Hungary

Austria

Portugal

Russian Federation

Vietnam

Ukraine

Can I get unique data from both the columns combined in Power BI using any DAX functions?

Re: DAX measure to find unique values from two columns

wdx223_Daniel — Fri, 17 Mar 2023 07:39:05 GMT

NewTable=

SUMMARIZE(UNION(VALUES('Table'[Country 1]),VALUES('Table'[Country 2])),'Table'[Country 1])

Re: DAX measure to find unique values from two columns

Anonymous — Fri, 17 Mar 2023 08:11:39 GMT

Create New Table

Countries = Summarize(Union(Values([Country1]),Values([Country2]),[Country1])

Re: DAX measure to find unique values from two columns

Anonymous — Fri, 17 Mar 2023 08:18:58 GMT

Thanks for the code, however this is throwing up an error

Re: DAX measure to find unique values from two columns

Anonymous — Fri, 17 Mar 2023 08:19:23 GMT

Thanks for the code, this is throwing up an error

Re: DAX measure to find unique values from two columns

Anonymous — Fri, 17 Mar 2023 09:39:34 GMT

Use Except function then.

Re: DAX measure to find unique values from two columns

Anonymous — Fri, 17 Mar 2023 10:04:19 GMT

Could you share the full code?

Re: DAX measure to find unique values from two columns

Anonymous — Tue, 21 Mar 2023 06:40:18 GMT

Hi Anonymous ,

Please try below steps:

1. below is my test table

Table:

2. create a new table with below dax formula

Table 2 = VAR _a = FILTER ( VALUES ( 'Table'[Country 1] ), 'Table'[Country 1] <> BLANK () ) VAR _b = FILTER ( VALUES ( 'Table'[Country 2] ), 'Table'[Country 2] <> BLANK () ) VAR _c = UNION ( _a, _b ) VAR _d = DISTINCT ( _c ) RETURN _d

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 measure to find unique values from two columns

Anonymous — Wed, 22 Mar 2023 04:41:06 GMT

Hi, thanks for the code but it is throwing up an error

Re: DAX measure to find unique values from two columns

Anonymous — Wed, 22 Mar 2023 05:31:12 GMT

Hi Anonymous ,

Could you please provide more information? it is very helpful for me to test.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu