Summarize Function Perfomance

[Kevin_Gitonga]

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
        )
    )
)

[parry2k]

@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.

[Kevin_Gitonga]

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

[parry2k]

@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 

[Kevin_Gitonga]

@parry2kHere's the sample pbix

https://1drv.ms/u/s!AsQQlQiSGgpSnxbNdUPv0Zs_xExG?e=za3Asx

[v-lid-msft]

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,

[parry2k]

@v-lid-msft sorry to say but that is not a solution, measure taking above 70000ms, it is painful and especially in this smaller dataset, you need to revisit your solution.

@Kevin_Gitonga I did had a chance to look at your report and did tweaked some of the measures but didn't get enough time to review all this but it is surely can run much faster than what you have already. I will try to get back to this asap, it required some debugging and have to look at each measure as there is dependency.

[Kevin_Gitonga]

@parry2kOkay

[v-lid-msft]

Hi @parry2k , 

Yes, you are right, 70s runtime cannot be a valid solutione I checked it again,  fix a averagex function, but it still need 50s to run for the entire year.  Due to it need to calculate condition for each datetime , brand and code (about 1.6M possible entire year), I have no idea how to optimize further, Looking forward to seeing a wonderful solution from you.

Measure 3 = 
VAR temp =
    CROSSJOIN ( VALUES ( pbi_Branches[Branch] ), VALUES ( pbi_Products[Code] ) )
RETURN
    SUMX (
        DISTINCT ( 'Dates'[Date] ),
        VAR d = 'Dates'[Date]
        VAR m3d =
            EDATE ( d, -3 ) + 1
        RETURN
            CALCULATE (
                COUNTROWS (
                    FILTER (
                        temp,
                        CALCULATE (
                            SUM ( 'pbi_Sales'[Qty] ),
                            ALL ( Dates[Date] ),
                            'Dates'[Date] >= m3d,
                            'Dates'[Date] <= d
                        ) > 0
                            && CALCULATE (
                                SUM ( pbi_SkuQuantity[Qty] ),
                                ALL ( Dates[Date] ),
                                'Dates'[Date] <= d
                            )
                                < DIVIDE (
                                    CALCULATE (
                                        SUM ( pbi_Sales[Qty] ),
                                        ALL ( Dates[Date] ),
                                        'Dates'[Date] >= m3d,
                                        'Dates'[Date] <= d
                                    ),
                                    CALCULATE (
                                        DISTINCTCOUNT ( pbi_Sales[InvDate] ),
                                        ALL ( Dates[Date] ),
                                        'Dates'[Date] >= m3d,
                                        'Dates'[Date] <= d
                                    )
                                ) * 2
                    )
                )
            )
    )

Best regards,