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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Emiel99
Frequent Visitor

Disctinct values in a Conactenatex with multiple values.

Hi All, I'm looking for a solution to a problem I'm having. 

I have two tabels: 

(Vendor) Stock Orders 2

FactKey            Supplier             SupplierCountry
FAC-001Supplier1NL
FAC-001Supplier2NL
FAC-002Supplier1NL
FAC-002Supplier3DE
FAC-003Supplier1NL

 

Posted Sales Invoice Subform (Q-MC)

(Supplier and SupplierCountry are calculated columns)

PostedSalesKey         Item         Amount          Supplier                              SupplierCountry
FAC-001Item1100Supplier1, Supplier2NL, NL
FAC-001Item2200Supplier1, Supplier2NL, NL
FAC-002Item1100Supplier1, Supplier3NL, DE
FAC-002Item3300Supplier1, Supplier3NL, DE
FAC-003Item1100Supplier1NL

Supplier is right, but I would like SupplierCountry to show only disctinct values. So for FAC-001 I would like to show NL only once. This is the code I'm using for the calculated colum:

 

 

 SupplierCountry = CONCATENATEX (
        FILTER (
            ALL ( '(Vendors) Stock Orders 2' ),
            '(Vendors) Stock Orders 2'[FactKey] = 'Posted Sales Invoice Subform (Q-MC)'[PostedSalesKey]
        ),
        '(Vendors) Stock Orders 2'[SupplierCountry],
        ", "
    )

 

 

Does anyone know how to solve this? I have tried adding Disctinct in diffrent places, but without succes.
Thanks in advance!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Emiel99 I *think*

 SupplierCountry = CONCATENATEX (
    DISTINCT(
      SELECTCOLUMNS(
        FILTER (
            ALL ( '(Vendors) Stock Orders 2' ),
            '(Vendors) Stock Orders 2'[FactKey] = 'Posted Sales Invoice Subform (Q-MC)'[PostedSalesKey]
        ),
        "__SupplierCountry", [SupplierCountry]
      )
    ),
    [__SupplierCountry],
        ", "
)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@Emiel99 I *think*

 SupplierCountry = CONCATENATEX (
    DISTINCT(
      SELECTCOLUMNS(
        FILTER (
            ALL ( '(Vendors) Stock Orders 2' ),
            '(Vendors) Stock Orders 2'[FactKey] = 'Posted Sales Invoice Subform (Q-MC)'[PostedSalesKey]
        ),
        "__SupplierCountry", [SupplierCountry]
      )
    ),
    [__SupplierCountry],
        ", "
)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Indeed this works, thanks!!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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