DAX measure to find unique values from two columns

[Soumik]

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?

[Anonymous]

Hi @Soumik ,

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.

[Soumik]

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

[Anonymous]

Hi @Soumik ,

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

[aditya0125]

Create New Table

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

[Soumik]

Thanks for the code, this is throwing up an error

[Soumik]

Could you share the full code?

[aditya0125]

Use Except function then.

[wdx223_Daniel]

NewTable=

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

[Soumik]

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