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

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's avatar
Anonymous
Not applicable
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