Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Thefae
Frequent Visitor

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:
Thefae_0-1625995040582.png


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]))
)

 

 

Thefae_1-1625995954233.png

 


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

 

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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 
    )
)



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
m3tr01d
Continued Contributor
Continued Contributor

Hi @Thefae,

how many years do you have in your Date table?

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

Fowmy
Super User
Super User

@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 
    )
)



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thefae
Frequent Visitor

That worked perfectly, thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.