Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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-001 | Supplier1 | NL |
FAC-001 | Supplier2 | NL |
FAC-002 | Supplier1 | NL |
FAC-002 | Supplier3 | DE |
FAC-003 | Supplier1 | NL |
Posted Sales Invoice Subform (Q-MC)
(Supplier and SupplierCountry are calculated columns)
PostedSalesKey | Item | Amount | Supplier | SupplierCountry |
FAC-001 | Item1 | 100 | Supplier1, Supplier2 | NL, NL |
FAC-001 | Item2 | 200 | Supplier1, Supplier2 | NL, NL |
FAC-002 | Item1 | 100 | Supplier1, Supplier3 | NL, DE |
FAC-002 | Item3 | 300 | Supplier1, Supplier3 | NL, DE |
FAC-003 | Item1 | 100 | Supplier1 | NL |
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!
Solved! Go to Solution.
@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],
", "
)
@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],
", "
)
Indeed this works, thanks!!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
80 | |
69 | |
60 |