topic Re: Dax Query optimization in DAX Commands and Tips

Dax Query optimization

joepath — Thu, 13 Jan 2022 11:37:18 GMT

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,

Re: Dax Query optimization

amitchandak — Thu, 13 Jan 2022 12:11:39 GMT

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

Re: Dax Query optimization

joepath — Sat, 15 Jan 2022 08:44:14 GMT

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.

Re: Dax Query optimization

mahoneypat — Sat, 15 Jan 2022 22:19:37 GMT

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

Re: Dax Query optimization

bcdobbs — Sat, 15 Jan 2022 23:18:02 GMT

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.

Re: Dax Query optimization

joepath — Sun, 16 Jan 2022 10:37:58 GMT

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

Re: Dax Query optimization

Anonymous — Fri, 18 Mar 2022 12:11:37 GMT

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.

Re: Dax Query optimization

m3tr01d — Fri, 18 Mar 2022 12:13:54 GMT

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