Forum Discussion
Summarize Function Perfomance
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
Hi Kevin_Gitonga ,
We can optimize it as following:
Measure 3 = var temp = CROSSJOIN(VALUES(pbi_Branches[Branch]),VALUES(pbi_Products[Code])) return SUMX(GROUPBY('Dates','Dates'[Date]),CALCULATE(COUNTROWS(FILTER(temp, CALCULATE ( [Sales P3M] ) > 0 && CALCULATE ( [Stock vs Buffer] ) < 0 ))))Measure 4 = VAR temp = CROSSJOIN ( VALUES ( pbi_Products[Code] ), VALUES ( pbi_Branches[Branch] ), VALUES ( Dates[Date] ) ) RETURN CALCULATE ( COUNTROWS ( FILTER ( temp, CALCULATE ( [Sales P3M] ) > 0 && CALCULATE ( [Stock vs Buffer] ) < 0 ) ) )But notice because table visual will list all the possible result so the table visual will consume much resource even do not use this measure.
Best regards,
