Forum Discussion

Thefae's avatar
Thefae
Frequent Visitor
5 years ago
Solved

DAX measure using dates uses too much memory

Hi,

 

I'm fairly new to Power BI and have found so much help on this site thank you!

I have a matrix visual that works fine in Desktop view, but exceeds memory usage when I publish it to app.powerbi.com (Since I usually work in Desktop - I'm not sure in which group to put my question)

 

The visual shows the "campaign revenue" for a few products in each their specific time period (their "campaign period").
I have a supporting small excel file with the SKU, start date ('kampagnemaal'[Startdato]), end date('kampagnemaal'[Slutdato]).The dates are different for each product:


My measure for works fine in desktop, but is veeeery slow. I need specific tips for changing my data or DAX to get below 1024 mb ram. 


Current measure:

Omsætning kampagneperiode = CALCULATE(SUM('fact Værdiposter (Value Entry)'[Sales Amount (Actual)]),
FILTER('Date', 'Date'[Date]>min('kampagnemaal'[Startdato])),
FILTER( 'Date', 'Date'[Date]<min('kampagnemaal'[Slutdato]))
)

 

 

 


Since the dates are only for this year, I tried filtering my data ('fact Værdiposter (Value Entry)'[Sales Amount (Actual)]) in powerquery to only show this year, but that made no visible difference.

 

I found out that my problem might be that "filter" is not the right way to go, but can't figure out which way to go then? 🙂


Hopefully, you can help!

Thank you, 

Theresa

 

 

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Thefae 

    Can you try this version please:

    Omsætning kampagneperiode =
var __Startdato  = MIN ( 'kampagnemaal'[Startdato] )
var __Slutdato = MIN ( 'kampagnemaal'[Slutdato] )
return
CALCULATE (
    SUM ( 'fact Værdiposter (Value Entry)'[Sales Amount (Actual)] ),
    FILTER ( 
        all('Date'), 
        'Date'[Date] > __Startdato && 'Date'[Date] < __Slutdato 
    )
)



4 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Thefae 

    Can you try this version please:

    Omsætning kampagneperiode =
var __Startdato  = MIN ( 'kampagnemaal'[Startdato] )
var __Slutdato = MIN ( 'kampagnemaal'[Slutdato] )
return
CALCULATE (
    SUM ( 'fact Værdiposter (Value Entry)'[Sales Amount (Actual)] ),
    FILTER ( 
        all('Date'), 
        'Date'[Date] > __Startdato && 'Date'[Date] < __Slutdato 
    )
)



    • Thefae's avatar
      Thefae
      Frequent Visitor
      5 years ago

      That worked perfectly, thank you!

    • Thefae's avatar
      Thefae
      Frequent Visitor
      5 years ago

      Only 3 🙂  Fowmys re-writing of my measure worked 🙂