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
MarkCBB
Helper V
Helper V

Slow Dax Meansure due to FILTER

Hi There,

 

I have the following measure that is really slow to calculate, It seems like it is the filter part of the measure that is the problem.

 

DSC 0-2 = 
Var MyData = SUMMARIZE('FACT',PRODUCTS[MATERIAL DESCRIPTION],STORES[STORE NAME],"_DSC",'MEASURES DSC'[AVERAGE DSC])
Return
DIVIDE(
    COUNTROWS(
        FILTER(MyData,[_DSC] >=0 && [_DSC] <3)),
    COUNTROWS(MyData),
0)

 

 

The Average DSC measure is as follows:

AVERAGE DSC = DIVIDE([CURRENT SOH],[AVERAGE DAILY UNITS (30DAYS)],0)

CURRENT SOH = 
Var TheLastDate = LASTDATE('FACT'[Date])
Return
 CALCULATE([TOTAL SOH],'FACT'[Date] = TheLastDate)

AVERAGE DAILY UNITS (30DAYS) = 
Var Days = 30
Var Total =  CALCULATE([TOTAL UNITS],DATESBETWEEN('CALENDAR'[Date],LASTDATE('CALENDAR'[Date])-(Days+1),LASTDATE('CALENDAR'[Date])))
Var DateRange = CALCULATE(DISTINCTCOUNT('CALENDAR'[Date]),DATESBETWEEN('CALENDAR'[Date],LASTDATE('CALENDAR'[Date])-(Days+1),LASTDATE('CALENDAR'[Date])))
Return
DIVIDE(Total,DateRange,0)

I have a standard dataset layout.

FACT, PRODUCT, STORES, CALENDAR. (all 1 to many)
sdf.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have also tried to do this without summarise but I get the same performance problems.

The fact table is 60M rows. 

The measures is going to to be used in a card as well as a product table, store table and a combination of Product + store table.

 

1 ACCEPTED SOLUTION

Hi @Anonymous

 

So I finally settled on the below, previously it took 2:10 seconds to calculate on DAX-Studio, the below takes 1.6 seconds.

I am pretty sure there is still a better way to do this, but 1.6 seconds uncached is workable. 

DEFINE
    VAR MyData =
        ADDCOLUMNS (
            SUMMARIZE ( 'FACT', PRODUCTS[Index], STORES[Index] ),
            "S", [AVERAGE DSC]
        )
EVALUATE
ROW (
    "Result", DIVIDE (
        COUNTROWS ( FILTER ( MyData, [S] >= 0 && [S] < 3 ) ),
        [DSC ROW COUNT BASE],
        0
    )
)

 

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

Any performance different if you write it like this?

DSC 0-2 = 
Var MyData = SUMMARIZE('FACT',PRODUCTS[MATERIAL DESCRIPTION],STORES[STORE NAME],"_DSC",'MEASURES DSC'[AVERAGE DSC])
Var numerator = CALCULATE(COUNTROWS(MyData), FILTER(MyData, [_DSC] >= 0, [_DSC] < 3))
Return
DIVIDE(
    numerator,
    COUNTROWS(MyData),
)
CheenuSing
Community Champion
Community Champion

Hi @MarkCBB

 

Can you post some sample data. And of the measures you had indicated which is the slowest.

 

You can download PowerBIHelper to analyse the queries and then optimise.

 

http://radacad.com/power-bi-helper

 

Cheers

 

CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing I will try the helper and let you know.

@Anonymous testing it now

@Anonymous, nope, it is just as slow and it brings back an incorrect result which is strange because everything looks correct (I fixed the FILTER to have && for both arguments.

 

Anonymous
Not applicable

Good spotting on the missing &&.  I wrote that pretty fast.  How much time is saved if you simply run this instead:

 

DSC 0-2 = 
Var MyData = SUMMARIZE('FACT',PRODUCTS[MATERIAL DESCRIPTION],STORES[STORE NAME],"_DSC",'MEASURES DSC'[AVERAGE DSC])
Return
COUNTROWS(MyData)

Naturally we want to get a base line for whether the filter is the correct place to investigate.  @CheenuSing has made a great sugestion.  Dax Studio is another avenue also.

@Anonymous, That measure is near instant.

I am also doing testing in DAX Studio 🙂

 

Anonymous
Not applicable

Let us know how you go with it.  You have me very curious.

Anonymous
Not applicable

I think the summarize function is partially to blame for your issues.  Here is something i found that might give you another idea:

 

https://insightsquest.com/2016/11/05/dax-query-tuning-example/

 

 

@Anonymous, let me give that a read and try and implement a similar approach. 

@Anonymous,

Quick update, while trying a few other approaches trying to narrow down the cost in performance, I tried this:

 

2_DSC 0-2 = 
Var MyData = SUMMARIZE('FACT',PRODUCTS[MATERIAL DESCRIPTION],STORES[STORE NAME],"_DSC",'MEASURES DSC'[AVERAGE DSC])
Return
DIVIDE(
    COUNTROWS(
        FILTER(MyData,[AVERAGE DSC] >=0 && [AVERAGE DSC] <3)),
    COUNTROWS(MyData),
0)

The Above uses the Average DSC measure in the FILTER instead of the column that was created in the SUMMARIZE, and the performance is ~70% faster. 

Still going to mess around a bit more with a few other ideas as well that the link you shared, updates to follow. 

Hi @Anonymous

 

So I finally settled on the below, previously it took 2:10 seconds to calculate on DAX-Studio, the below takes 1.6 seconds.

I am pretty sure there is still a better way to do this, but 1.6 seconds uncached is workable. 

DEFINE
    VAR MyData =
        ADDCOLUMNS (
            SUMMARIZE ( 'FACT', PRODUCTS[Index], STORES[Index] ),
            "S", [AVERAGE DSC]
        )
EVALUATE
ROW (
    "Result", DIVIDE (
        COUNTROWS ( FILTER ( MyData, [S] >= 0 && [S] < 3 ) ),
        [DSC ROW COUNT BASE],
        0
    )
)

 

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.