Forum Discussion
Summarize Function Perfomance
I have this model Dimension Tables of Branches, Products and Dates, and a fact table of Stocks and Sales.
I need to count the number of products per branch per day where the stock was below the buffer level.
I have the folllowing measure which works, however it takes a long time to compute often using up alot of memory.Is there a way to optimize the speed of this measure so that I can Plot it on a line graph and use it in a table without intensive resource consumption?
Below Buffer SKUs =
CALCULATE(
COUNTROWS(
FILTER(
SUMMARIZE(
CROSSJOIN(VALUES(pbi_Products[Code]),VALUES(pbi_Branches[Branch]),VALUES(Dates[Date])),
pbi_Branches[Branch],pbi_Products[Code],Dates[Date],
"Active Products",[Sales P3M],
"vs Buffer",[Stock vs Buffer]
),
[Active Products]>0 &&[vs Buffer]<0
)
)
)
8 Replies
Kevin_Gitonga not sure how you are visualizing the data, let's go very basic here, use columns from dimension table and aggregation from each table, in the table visual and you should get the correct information and from there we can build the business logic.
Okay this is how I am visualizing the data where for each day, branch and Product code I highlight the products where the Stock vs Buffer is less than 0 as indicated in red. The business logic is to come up with a ratio for this that counts the number of products for each day in each branch where the stock vs buffer is less than 0.
e.g in this screen shot the count would be 3
Kevin_Gitonga what are stock and buffer, are these measures? Can you share pbix file to get you the solution? Remove any sensitivie before sharing
