How to concatenate distinct values of a column, which is generated by multiple filters?

[Funk-E-Guy]

I have two relevant tables that I'm working on;

• Sales History - A table that reports the number of each product sold each day by country
• Promotions - A table which tracks which dates a promotion is active for any given products, and what type of promotion is active (discount, sale, coupon, etc). It's entirely possible for multiple promotions to be run on a single product on any given day.

My goal is to have a column in my Sales History table, which is a concatenation of all promotion types active for that product on any given day.

I still don't have a full understanding about how all of the formulas interact with each other. Below is a mix of pseudocode and my attempt at the actual code.

Active Promotions = 

concatenate:
    calculate(
        DISTINCT('Promotions'[Type]), 
        filter('Promotions', 'Promotions'[ASIN] = earlier([ASIN])), 
        filter('Promotions', 'Promotions'[Country] = earlier([Country])), 
        filter('Promotions', 'Promotions'[Date] = earlier([Date])),
        AllEXCEPT('Promotions','Promotions'[Type],'Promotions'[ASIN],'Promotions'[Country],'Promotions'[Date])
    )

Here's an example of my promotions table:

ASIN	Country	Type	Date
TestASIN	US	Coupon	12/1/2021
TestASIN	US	Coupon	12/2/2021
TestASIN	US	Coupon	12/3/2021
TestASIN	US	Coupon	12/4/2021
TestASIN	US	Lightning Deal	12/1/2021

And here's an example of my Sales History table, with the desired column added at the end.

ASIN	Country	Date	QTY	Active Promotions
TestASIN	US	11/30/2021	100
TestASIN	US	12/1/2021	500	Coupon, Lightning Deal
TestASIN	US	12/2/2021	120	Coupon
TestASIN	US	12/3/2021	120	Coupon
TestASIN	US	12/4/2021	120	Coupon
TestASIN	US	12/5/2021	100

[smpa01 · Accepted solution]

@Funk-E-Guy  you can try this

_desired = 
CALCULATE (
    CALCULATE (
        CONCATENATEX ( Promotions, Promotions[Type], ",", Promotions[Type], ASC ),
        TREATAS (
            SUMMARIZE (
                SalesHistory,
                SalesHistory[Country],
                SalesHistory[Date],
                SalesHistory[ASIN]
            ),
            Promotions[Country],
            Promotions[Date],
            Promotions[ASIN]
        )
    )
)

[Funk-E-Guy]

This seems to do the trick!

Thanks!

[parry2k]

@Funk-E-Guy add a column using this:

Active Promotions = 
VAR __tbl = CALCULATETABLE ( VALUES ('Text'[Type] ), ALLEXCEPT ( 'Text', 'Text'[ASIN],'Text'[Date],'Text'[Country] ) )
RETURN
CONCATENATEX ( __tbl, [Type], "," )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smpa01]

@Funk-E-Guy  can you please try this as a measure

Measure1= --assuming there is no relationship
VAR _country =
    MAX ( 'Sales History'[Country] )
VAR _day =
    MAX ( 'Sales History'[Day] )
VAR _product =
    MAX ( 'Sales History'[Product] )
RETURN
    CONCATENATEX (
        FILTER (
            Promotions,
            Promotions[Country] = _country
                && Promotions[Day] = _day
                && Promotions[Product] = _product
        ),
        Promotions[Type],
        ",",
        Promotions[Type], ASC
    )
---------------------------------------------------------------------------------------------
Measure2= --assuming there is a relationship
VAR _country =
    MAX ( 'Sales History'[Country] )
VAR _day =
    MAX ( 'Sales History'[Day] )
VAR _product =
    MAX ( 'Sales History'[Product] )
RETURN
    CONCATENATEX (
        FILTER (
            ALL(Promotions),
            Promotions[Country] = _country
                && Promotions[Day] = _day
                && Promotions[Product] = _product
        ),
        Promotions[Type],
        ",",
        Promotions[Type], ASC
    )

[Funk-E-Guy]

FYI I edited my post to include some sample data and desired output, in case that changes your answer. Is it possible to do this without the use of measures?

[parry2k]

@Funk-E-Guy it will be easier if you provide some sample data and the expected output.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Funk-E-Guy]

Post edited