Help : DAX optimization

[Baskar]

Thanks Advanced !!!

This is my DAX measure to calculated the sales made >4500 with in 3 transaction. 

Here am getting expected result but the measure perfomance is very poor. 

Test Measure =
VAR _curdate =
    MAX ( 'Date'[Date] )
VAR _prevdate =
    DATE ( YEAR ( _curdate ), MONTH ( _curdate ) - 12, DAY ( _curdate ) )
VAR _table =
    CALCULATE (
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    Sales,
                    Sales[LoyaltyId],
                    Sales[Bill Flag],
                    "BuyingVal", SUM ( Sales[NetSaleAmount] ),
                    "BuyingFreq", DISTINCTCOUNT ( Sales[Original Bill Num] )
                ),
                Sales[Bill Flag] = 1
                && [BuyingVal] > 4500
                && [BuyingFreq] < 3
            )
        ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] > _prevdate && 'Date'[Date] <= _curdate )
    )
RETURN
    _table

Help me to come over from here. 
@ImkeF @parry2k @Zubair_Muhammad @Ashish_Mathur @Greg_Deckler @MFelix @MattAllington 

[Zubair_Muhammad]

@Baskar 

Try this one

Test Measure =
VAR _curdate =
    MAX ( 'Date'[Date] )
VAR _prevdate =
    DATE ( YEAR ( _curdate ), MONTH ( _curdate ) - 12, DAY ( _curdate ) )
VAR filtered_table =
    FILTER ( Sales, Sales[Bill Flag] = 1 )
VAR _table =
    CALCULATE (
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    filtered_table,
                    [LoyaltyId],
                    [Bill Flag],
                    "BuyingVal", SUM ( Sales[NetSaleAmount] ),
                    "BuyingFreq", DISTINCTCOUNT ( Sales[Original Bill Num] )
                ),
                [BuyingVal] > 4500
                    && [BuyingFreq] < 3
            )
        ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] > _prevdate && 'Date'[Date] <= _curdate )
    )
RETURN
    _table

[Baskar]

Hi @Zubair_Muhammad 

Thanks for your response my friend. 

While using your code am not getting expected result.

While using the var 

VAR filtered_table =
    FILTER ( Sales, Sales[Bill Flag] = 1 )

This condition is not working 

  FILTER ( ALL ( 'Date' ), 'Date'[Date] > _prevdate && 'Date'[Date] <= _curdate )

[ImkeF]

Does this work better?:

Test Measure =
VAR _curdate =
    MAX ( 'Date'[Date] )
VAR _prevdate =
    DATE ( YEAR ( _curdate ), MONTH ( _curdate ) - 12, DAY ( _curdate ) )
VAR filtered_table =
    CALCULATETABLE (
        FILTER ( Sales, Sales[Bill Flag] = 1 ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] > _prevdate && 'Date'[Date] <= _curdate )
    )
VAR _table =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE ( filtered_table, [LoyaltyId], [Bill Flag] ),
                "BuyingVal", SUM ( Sales[NetSaleAmount] ),
                "BuyingFreq", DISTINCTCOUNT ( Sales[Original Bill Num] )
            ),
            [BuyingVal] > 4500
                && [BuyingFreq] < 3
        )
    )
RETURN
    _table

[Baskar]

Thanks @ImkeF 

While using your code am getting unexpected results ( getting less count ).

Still am helpless

[ImkeF]

How about the performance?

[Baskar]

Better then previous. It is great.
But result completly demaged 🙂

[ImkeF]

I would suggest to check all filters or split it up into more variables to check. I might have typos in my code, as I couldn't test the code.

Maybe these functions will help you as well: https://www.thebiccountant.com/2019/07/04/debug-dax-variables-in-power-bi-and-power-pivot/

[ImkeF]

Sorry, my bad, forgot the CALCULATE in the ADDCOLUMNS:

Test Measure =
VAR _curdate =
    MAX ( 'Date'[Date] )
VAR _prevdate =
    DATE ( YEAR ( _curdate ), MONTH ( _curdate ) - 12, DAY ( _curdate ) )
VAR filtered_table =
    CALCULATETABLE (
        FILTER ( Sales, Sales[Bill Flag] = 1 ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] > _prevdate && 'Date'[Date] <= _curdate )
    )
VAR _table =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE ( filtered_table, [LoyaltyId], [Bill Flag] ),
                "BuyingVal", CALCULATE ( SUM ( Sales[NetSaleAmount] ) ),
                "BuyingFreq", CALCULATE ( DISTINCTCOUNT ( Sales[Original Bill Num] ) )
            ),
            [BuyingVal] > 4500
                && [BuyingFreq] < 3
        )
    )
RETURN
    _table

[Baskar]

Thanks @ImkeF , FYI I have already added calculate in addcolumn earlier. No luck

[ImkeF]

I don't see what else I can do without any data to compare against here.

So you might consider creating some sample data with source and desired outcome so we can narrow down the problem.

[Baskar]

@ImkeF  Here i have attached power bi file with sample data. 

Example : 

For  Jan 2015 expected result is 80, but we are getting 3. 

Refer the first tab for raw data. 

[ImkeF]

THanks, but I cannot spot the measure that returns the correct figures.

What's its name?  - and if it isn't included, please include it as well.

[Anonymous · Accepted solution]

@Baskar  - The following will give you 80 for Jan 2015 in your file:

Checking 2 = 
var _cur = MAX('Date Master'[Start Date])
var _pre = DATE(YEAR(_cur),MONTH(_cur)-12,DAY(_cur))
var _filter = 
CALCULATETABLE(
    Orders, 
    Orders[Ship Mode]="Standard Class",
    FILTER(ALL('Date Master'),'Date Master'[Start Date] >_pre && 
            'Date Master'[Start Date] <= _cur))
var _summed = 
SUMMARIZE(
    _filter,
    [Customer ID],
    "sales",SUM([Sales]),
    "count",DISTINCTCOUNT([Order ID])                       
)
return  COUNTROWS(filter(_summed,[sales] >1000 && [count] <5))

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[sokg]

Hi @Baskar 

Try to use SUMMARIZE  with addcolumns as the Italians said at the link below.

https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

[Baskar]

Thanks for your quick response my dear friend. 

There is no luck even tried with addcolumn. 

😞

It is taking 1.5 mins