Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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
)
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
Hi @Anonymous
I guess you have more than three products?
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 |
@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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |