Calculated Column to get Flag based on the values for a key

[Anonymous]

Hi,

I have data as below:

Customer	Products
A	Bread
B	Bread
B	Butter
C	Bread
C	Egg
D	Bread
D	Butter
D	Egg
E	Butter
F	Egg

I want to create a calculated column using DAX as a flag indicating whether a customer got only Bread, Bread and Butter, Bread and Egg, Only Butter, Only Egg, All etc as below.

Customer	Products	Flag
A	Bread	Bread only
B	Bread	Bread and Butter
B	Butter	Bread and Butter
C	Bread	Bread and Egg
C	Egg	Bread and Egg
D	Bread	All
D	Butter	All
D	Egg	All
E	Butter	Butter Only
F	Egg	Egg Only

Kindly help with the DAX calculated column for the same.

Thanks,

Prajna

[daXtreme · Accepted solution]

Assuming that your table is named CustProd...

Flag = // calculated column
var CurrentCustomer = CustProd[Customer]
var PartOfTableOfInterest =
    FILTER(
        CustProd,
        // Please keep column names in singular.
        CustProd[Customer] = CurrentCustomer
    )
var TotalNumberOfProducts =
    COUNTROWS(
        SELECTCOLUMNS(
            PartOfTableOfInterest,
            "@Product", CustProd[Product]
        )
    )
var PartialOutput =
    CONCATENATEX(
        PartOfTableOfInterest,
        CustProd[Product],
        IF( TotalNumberOfProducts = 2, " and ", "" ),
        CustProd[Product],
        ASC
    )
var Output = 
    SWITCH( true(),
        TotalNumberOfProducts = 1, PartialOutput & " Only",
        TotalNumberOfProducts > 2, "All",
        PartialOutput
    )
return
    Output

[daXtreme · Accepted solution]

Assuming that your table is named CustProd...

Flag = // calculated column
var CurrentCustomer = CustProd[Customer]
var PartOfTableOfInterest =
    FILTER(
        CustProd,
        // Please keep column names in singular.
        CustProd[Customer] = CurrentCustomer
    )
var TotalNumberOfProducts =
    COUNTROWS(
        SELECTCOLUMNS(
            PartOfTableOfInterest,
            "@Product", CustProd[Product]
        )
    )
var PartialOutput =
    CONCATENATEX(
        PartOfTableOfInterest,
        CustProd[Product],
        IF( TotalNumberOfProducts = 2, " and ", "" ),
        CustProd[Product],
        ASC
    )
var Output = 
    SWITCH( true(),
        TotalNumberOfProducts = 1, PartialOutput & " Only",
        TotalNumberOfProducts > 2, "All",
        PartialOutput
    )
return
    Output

[tamerj1]

Hi @Anonymous 

I guess you have more than three products? 

[Anonymous]

Hi @tamerj1 ,

Yes. There are other products. But my focus in only on Bread, Butter and Egg.
Ex:

Customer	Products	Flag
A	Bread	Bread only
B	Bread	Bread and Butter
B	Butter	Bread and Butter
C	Bread	Bread and Egg
C	Egg	Bread and Egg
D	Bread	All
D	Butter	All
D	Egg	All
E	Butter	Butter Only
F	Egg	Egg Only
G	Bread	Bread and Butter
G	Butter	Bread and Butter
G	Jam	Bread and Butter

[tamerj1 · Accepted solution]

@Anonymous 
Please refer to attached sample file with the solution

Flag = 
VAR FocusProducts = { "Bread", "Butter", "Egg" }
VAR CurrentCustomerProducts = CALCULATETABLE ( VALUES ( Data[Products] ), ALLEXCEPT ( Data, Data[Customer] ) )
VAR MatchingProducts = INTERSECT ( CurrentCustomerProducts, FocusProducts )
VAR Concatenation = CONCATENATEX ( MatchingProducts, [Products], " And ", [Products], ASC )
RETURN
    SWITCH (
        TRUE ( ),
        COUNTROWS ( FocusProducts ) = COUNTROWS ( MatchingProducts ), "All",
        COUNTROWS ( MatchingProducts ) = 1, Concatenation & " Only",
        Concatenation
    )