Dax Query optimization

[joepath]

Hello,

Below query is for Total Sum of the positive values in tablur report, working as expected but liitle slow.

How to rewrite it in optimize way?

Correct Total Sum = SUMX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(Fact,dimA[Aname],dimB[BName]),
"@MeasureA",[MeasureA],
"@MeasureB",[MeasureB)],
"@MeasureC",[MeasureC]

),
[@MeasureA]>0 && [@MeasureB]>0 && [@MeasureC]>0
)
,[@MeasureA]
)

Thanks,

[m3tr01d]

@joepath Most likely, the problem are inside the 3 measures you are using.

Please give us the exact code of the three measures.

Thank you

[Daryl-Lynch-Bzy]

@joepath - I agree with the others that you query is structured well.  A small suggestion is to possibly remove "dimA[Aname], dimB[BName]" from the SUMMARIZE function.  Instead you could summarize by the FK_A and FK_B on the main fact table.  There is a small performance gain in the SUMMARIZE step because the query "may not" need JOINS between the Fact table and DimA and DimB tables.  However, this would not work through if "dimA[Aname]" and "dimB[BName]" are sets/groups of many FK_A and FK_B, or when the Filter Context applying to the Fact table is referencing these table anyway.

[bcdobbs]

Is there any relationship between measure a, b and c. Just wondering if you could look at it from a maths point of view as to what needs to be true for all 3 to be greater than zero. That might be faster to test than actually evaluating the full values.

If you create a calculated table which materialises the table inside the sumx how many rows do you get? I agree with the others. Initial thought is it looks "ok". How slow is the query?

Only other thought is depending on what you need to slice by are you able to preagregate anything in a calculated table to move some evaluation away to model processing instead of live at query time.

[mahoneypat]

I agree that measure looks pretty good, and would have written it the same way. How big is your Fact table and how many rows should that SUMMARIZE table have?  One thing you could try would be to filter it sequentially with FILTER(FILTER(FILTER. Not sure it will help much but easy to try.

Correct Total Sum =
SUMX (
    FILTER (
        FILTER (
            FILTER (
                ADDCOLUMNS (
                    SUMMARIZE ( Fact, dimA[Aname], dimB[BName] ),
                    "@MeasureA", [MeasureA]
                ),
                [@MeasureA] > 0
            ),
            [MeasureB] > 0
        ),
        [MeasureC] > 0
    ),
    [@MeasureA]
)

Pat

[joepath]

Thanks all for looking into this, seems not much changes is required,

[amitchandak]

@joepath , this seems fine, need to check calculation for three measures

[joepath]

@amitchandak  Measure have basic avg calculations, Is there any way to replace filter function with calculatedtable? I see filter always create prob on a big table.