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
Anonymous
Not applicable

Slow measure with calculate and filter

Hello,

 

I have a working measure, parsing through over 5 million rows (in a table named 'Overview'), which gives me the number of unique customers who have ordered for over 500 EUR worth of goods in a given period, with below info on each row:

 

Order# | Customer | NIS (Net Invoice Sales) | Calendar Days

 

 

DH500 Q1 = CALCULATE(DISTINCTCOUNT(Overview[Customer]);DATESBETWEEN('Calendar'[Calendar Days];"01/07/2019";"30/09/2019");filter(Overview;sum(Overview[NIS])>500))

 

 

While this measure calculated things properly at customer level, when I wanted a total by sales rep, total was wildly inaccurate (still unclear as why), which brought me to then use this measure for a sales rep total:

 

 

DH 500 Q1 = sumx(SUMMARIZE(Overview;Overview[Customer];"SUM";[DH500 Q1]);[DH500 Q1])

 

 

However, it is now painfully slow to calculate (over 5 min when filtered on ONE sales rep), meaning I cannot use it "on the go".

 

I am aware that FILTER drastically slows down measure calculation, and was hoping one of you guys would be able to optimize the measure calculation to something more acceptable... I was looking to use summarize on a wider level, maybe fully replacing FILTER but without any success so far.

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Calcualtion for two kinds of suitation.

sum of [NIS] at customer level,

 

sum>500 =
VAR n =
    CALCULATE (
        SUM ( Sheet3[NIS] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[Customer] = MAX ( Sheet3[Customer] )
                && Sheet3[date] >= MIN ( 'disconnected date'[Date] )
                && Sheet3[date] <= MAX ( 'disconnected date'[Date] )
        )
    )
RETURN
    IF ( n > 500, n )

 

sum of [NIS] at total level,

 

total sum = SUMX(ALL(Sheet3[Customer]),[sum>500])

 

 

Distinctcount of customer who's NIS is over 500 at customer level

 

customer level =
VAR n =
    CALCULATE (
        SUM ( Sheet3[NIS] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[Customer] = MAX ( Sheet3[Customer] )
                && Sheet3[date] >= MIN ( 'disconnected date'[Date] )
                && Sheet3[date] <= MAX ( 'disconnected date'[Date] )
        )
    )
RETURN
    IF ( n > 500, 1 )

 

Total Distinctcount of customer who's NIS is over 500 (at total level)

 

total discount = CALCULATE(DISTINCTCOUNT(Sheet3[Customer]),FILTER(Sheet3,Sheet3[customer level]=1))

 

 In my test file, disconnected date table has no relationship with other tables.

 

disconnected date = CALENDARAUTO()

 

Capture4.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Calcualtion for two kinds of suitation.

sum of [NIS] at customer level,

 

sum>500 =
VAR n =
    CALCULATE (
        SUM ( Sheet3[NIS] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[Customer] = MAX ( Sheet3[Customer] )
                && Sheet3[date] >= MIN ( 'disconnected date'[Date] )
                && Sheet3[date] <= MAX ( 'disconnected date'[Date] )
        )
    )
RETURN
    IF ( n > 500, n )

 

sum of [NIS] at total level,

 

total sum = SUMX(ALL(Sheet3[Customer]),[sum>500])

 

 

Distinctcount of customer who's NIS is over 500 at customer level

 

customer level =
VAR n =
    CALCULATE (
        SUM ( Sheet3[NIS] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[Customer] = MAX ( Sheet3[Customer] )
                && Sheet3[date] >= MIN ( 'disconnected date'[Date] )
                && Sheet3[date] <= MAX ( 'disconnected date'[Date] )
        )
    )
RETURN
    IF ( n > 500, 1 )

 

Total Distinctcount of customer who's NIS is over 500 (at total level)

 

total discount = CALCULATE(DISTINCTCOUNT(Sheet3[Customer]),FILTER(Sheet3,Sheet3[customer level]=1))

 

 In my test file, disconnected date table has no relationship with other tables.

 

disconnected date = CALENDARAUTO()

 

Capture4.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @v-juanli-msft ,

 

First of all, thanks a lot for the time you took to prepare and draft this reply, it is much appreciated.

 

My reply is late because I spent most of my week trying to make it work or tweak it, yet I always end up with 2 results :

 

1/ Out of memory allocation (although I only have 5 million rows)

2/ 

CALCULATE(DISTINCTCOUNT(Overview[Customer]);filter(Overview;sum(Overview[NIS])>500);DATESBETWEEN('Calendar'[Date];"01/07/2019";"31/03/2020"))


give the exact same result as 

CALCULATE(DISTINCTCOUNT(Overview[Customer]);DATESBETWEEN('Calendar'[Date];"01/07/2019";"31/03/2020"))

 

Meaning i somehow cannot calculate a basic sumif...

 

Kind regards,

 

Brendan

JarroVGIT
Resident Rockstar
Resident Rockstar

It might because of your DATESBETWEEN() function. Is there a reason why these values are hard coded or are they normally in a slicer in the report? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.