Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Gérard Ducouret]

Hi,
I was reading that:

UNION performs the union of two tables. It is mainly used in calculated columns.

In this example the opposite table contains all the countries from both the “Customer” and the “Store” tables.

UNION does not remove duplicates before returning a result.

So I tried this little code:

AllCountryRegions =             — New Table

UNION (

      ALL ( Customer[CountryRegion] ),

      ALL ( Store[CountryRegion] )

)

and I noticed that all the duplicates are removed!
So, where is the truth ?

Thanks for your help

 

——————————
Gérard Ducouret
——————————

[Ben Howard]

Hi Gerard, I have certainly written DAX where I have used UNION in conjunction with DISTINCT to remove duplicates, so I would check your code and input.

——————————
Ben Howard, UK.

Please mark any answer as recommended if it helps you.
——————————
——————————————-

[Gérard Ducouret]

Hi Ben, If you want, you can check, in the attached file, the “AllCountryRegion” calculated table, tab “Page 1”

Thanks

——————————
Gérard Ducouret
——————————
——————————————-

[Ben Howard]

Hi Gerard,

If you look at the data tab, you’ll see you have 63 rows, but 34 distinct values.  Try sorting the column ascending and you’ll see the duplicates.

If you then wrap a distinct around it, you’ll only return 34 values.

——————————
Ben Howard, UK.

Please mark any answer as recommended if it helps you.
——————————
——————————————-

[Gérard Ducouret]

Hi Ben,

Thanks a lot for these detailed explanations!

——————————
Gérard Ducouret
——————————
——————————————-

[William Skelley]

Hi Gerard:

If you use the function DISTINCT before UNION    DISTINCT(

UNION( …

This action will remove duplicates.

I hope this helps you.

Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-

[Gérard Ducouret]

Hi Bill,
Great ! Very interesting !
Thanks a lot

——————————
Gérard Ducouret
——————————
——————————————-

[William Skelley]

Your welcome!

I didn’t notice Ben had taken a through approach towards the solution too!

Best regards,

Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-