The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a dim table Customer, dim table Product and a fact table Sales (and other dim tables like date, stores, ...).
What i want to achieve is to mark Customers and Products, when the Customer buy product type A and B with an amount > 100.
Basicaly, I want a list of customer and their products, when the customer buy both product type A and B with an amount greater than 100.
Ex:
Customer Product ProductType Amount WantedMeasureResult
1 11 A 100 0, because Cust1 buy only
1 12 A 200 0, because Cust1 buy only
2 13 A 100 0, because Cust2 buy both A and B but the amount is not > 100
2 14 A 200 1, because Cust2 buy both A and B and the amount is > 100
2 15 B 200 1, because Cust2 buy both A and B and the amount is > 100
Thanks.
@Anonymous @amitchandak
Thanks for your answers, but they dont work.
I am writing a measure like below but not sure how to write the return part...
(Core and CAP is like type A and B, the model is a star shema, dim filters the data table and it is all 1 to many.)
WantedResultMeasure =
var x=CALCULATE(CONCATENATEX(VALUES(t1[Type]),t1[Type],",",t1[Type],ASC),ALLEXCEPT(t1,t1[Customer]))
var y=Len(x)
Return
IF(y>1,
IF(SUM(t1[Amount])>100,
"1, because Cust"&SELECTEDVALUE(t1[Customer])& " buy both A and B and the amount is > 100",
"0, because Cust"&SELECTEDVALUE(t1[Customer])& " buy both A and B but the amount is not > 100"),
"0, because Cust"&SELECTEDVALUE(t1[Customer])& " buy only")
@danning1234 , Try like
if( calculate( distinctcount([ProductType]),filter(Table,[Customer] =earlier([Customer]) && [Amount] > 100 && [ProductType] in {"A","B"}))=2,1,0)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
17 | |
15 |
User | Count |
---|---|
56 | |
31 | |
30 | |
24 | |
22 |