Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I created a measure below (Latest 13 weeks buying) that tells me the number of weeks over the latest 13 weeks that an account has purchased any product. It works perfectly. Based on this measure value, I have another measure (brand loyalty) that assigns a value based on the measure result.
Latest 13 Weeks Buying = calculate(distinctcount(Sale[Week #]), datesinperiod(CalendarTable[Date], LASTDATE(all(Sale[InvoiceDate])), -91, day), Sale[Quantity]>0, SupplierItem[Draft/Package]="D")
Brand Loyalty = if([Latest 13 Weeks Buying]>=9, "Continuous", if(and([Latest 13 Weeks Buying]<=4, [Latest 13 Weeks Buying]>0), "Rotation", if(and([Latest 13 Weeks Buying]>=5, [Latest 13 Weeks Buying]<9), "Frequent", "Non-Buying")))
I can create a table in Power BI that shows me the Account Name with the Latest 13 weeks Buying and Brand Loyalty.
The issue that i'm running into, is I want to be able to summarize the # of accounts by brand loyalty bucket. Because the formula is currently in a measure, when I try to get the count of accounts, it sums all acounts and has everything falling under the 'continuous' bucket. Nothing shows up for rotation, frequent, non-buying, etc.
I tried putting my latest 13 weeks buying as a calculated column instead of measure (same syntax). Issue here is that if an account purchased 4 times in one week, it inflates the latest 13 weeks buying number. The column on the left in the below picture is the calculated column result and column on the right is the measure.
Please let me know if you have any ideas on how to solve this. I've tried altering it to the below calculated column (Latest 13 weeks buying2) but it freezes my power bi workbook everytime. I know that I need to assign a buying loyalty at the row level but not entirely sure how to without running into the above issue where it is doublestating the distinct week count.
Latest 13 Weeks Buying2 = calculate(distinctcount(Sale[Week #]), datesinperiod(CalendarTable[Date], lastdate(all(sale[InvoiceDate])), -91, day), Sale[Quantity]>0, SupplierItem[Draft/Package]="D", filter(all(sale), sale[AccountKey]= earlier(sale[AccountKey])))
Solved! Go to Solution.
I was able to solve the issue I was running into by creating a new table to assign each account a measure value by using the summarize columns formula:
Draft Buying =
summarizecolumns(Sale[AccountName], "13 Weeks Buying", [Latest 13 Weeks Buying], "13 Week CE", [13 Wk Draft CE], "13 Week BBL", [13 WK DRaft BBL], "13 Week Unit", [13 Wk Units], "LY 13 Week CE", [LY 13 Wk Draft CE], "LY 13 Week BBL", [LY 13 WK Draft BBL], "LY 13 Week Unit", [LY 13 Wk Units], "Total CE", [Total Draft CE])
I was able to solve the issue I was running into by creating a new table to assign each account a measure value by using the summarize columns formula:
Draft Buying =
summarizecolumns(Sale[AccountName], "13 Weeks Buying", [Latest 13 Weeks Buying], "13 Week CE", [13 Wk Draft CE], "13 Week BBL", [13 WK DRaft BBL], "13 Week Unit", [13 Wk Units], "LY 13 Week CE", [LY 13 Wk Draft CE], "LY 13 Week BBL", [LY 13 WK Draft BBL], "LY 13 Week Unit", [LY 13 Wk Units], "Total CE", [Total Draft CE])
User | Count |
---|---|
87 | |
84 | |
67 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |