Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a traditional sales sheet, name of the product, name of the customer, volume purchased and date of purchased. This file gets updated every single day. In the company we are looking into a program approach, where if a customer buys certain 3 products, not necessarly at the same time, let's same within the year; and those products contains the words "AMP", and the other product contains "LOL", and another product contains "HFR" this customer is a let's call a "SUPER" customer. If a customer buys only 2 of those products than this customer is "NOT SO SUPER".
any suggestion on how to accomplish this?
thank you
Gustavo
Solved! Go to Solution.
Hi @gusdcruz ,
Here I create a measure in my sample pbix.
Measure = VAR SUPER = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[customer], Table1[date].[Year] ), "LOL" IN VALUES ( Table1[Product] ) && "AMP" IN VALUES ( Table1[Product] ) && "HFR" IN VALUES ( Table1[Product] ) ) ) VAR NOTSO = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[customer], Table1[date].[Year] ), AND ( "LOL" IN VALUES ( Table1[Product] ), "AMP" IN VALUES ( Table1[Product] ) ) || AND ( "LOL" IN VALUES ( Table1[Product] ), "HFR" IN VALUES ( Table1[Product] ) ) || AND ( "HFR" IN VALUES ( Table1[Product] ), "AMP" IN VALUES ( Table1[Product] ) ) ) ) RETURN IF ( SUPER = 3, "Super", IF ( NOTSO = 2, "Not So super", "na" ) )
Pbix as attached.
Regards,
Frank
Hi @gusdcruz ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Regards,
Frank
Hi @gusdcruz ,
Here I create a measure in my sample pbix.
Measure = VAR SUPER = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[customer], Table1[date].[Year] ), "LOL" IN VALUES ( Table1[Product] ) && "AMP" IN VALUES ( Table1[Product] ) && "HFR" IN VALUES ( Table1[Product] ) ) ) VAR NOTSO = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[customer], Table1[date].[Year] ), AND ( "LOL" IN VALUES ( Table1[Product] ), "AMP" IN VALUES ( Table1[Product] ) ) || AND ( "LOL" IN VALUES ( Table1[Product] ), "HFR" IN VALUES ( Table1[Product] ) ) || AND ( "HFR" IN VALUES ( Table1[Product] ), "AMP" IN VALUES ( Table1[Product] ) ) ) ) RETURN IF ( SUPER = 3, "Super", IF ( NOTSO = 2, "Not So super", "na" ) )
Pbix as attached.
Regards,
Frank
Hi @gusdcruz ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Regards,
Frank
Sorry, Frank. I havent had a chance to work on this. Other porjects come around since my initial request here. I will let you know as soon as I can. I really appreciate your time helping me out here.
User | Count |
---|---|
99 | |
87 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |