Forum Discussion

Baskar's avatar
Baskar
Resident Rockstar
7 years ago
Solved

Help : DAX optimization

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 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.
    Nathan Peterson

15 Replies

    • Baskar's avatar
      Baskar
      Resident Rockstar
      7 years ago

      Thanks for your quick response my dear friend. 

       

      There is no luck even tried with addcolumn. 

       

      :(

       

      It is taking 1.5 mins 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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's avatar
      Baskar
      Resident Rockstar
      7 years ago

      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's avatar
        ImkeF
        Community Champion
        7 years ago

        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