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

       

      • Baskar's avatar
        Baskar
        Resident Rockstar
        7 years ago

        Thanks ImkeF 

         

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

         

        Still am helpless