DAX Measure is very slow.

[Anonymous]

Hi Expert

I understand there is a lot going on here, but any tips on how i could speed up running the following dax measure, in total i have three measure similiar to the example shown which according to DAX Studio take 94520 miliseconds to run.

NoRep =
VAR __table =
    SUMMARIZE ( ALL ( PMS_COMPLAINT ), [Products] )
VAR __table1 =
    ADDCOLUMNS (
        __table,
        "__p-value", CALCULATE ( [p-value], ALL ( PMS_COMPLAINT[PMSReportability] ) ),
        "__TAU", CALCULATE ( [TAU 1], ALL ( PMS_COMPLAINT[PMSReportability] ) ),
        "__trend", CALCULATE ( [__trend], ALL ( PMS_COMPLAINT[PMSReportability] ) )
    )
VAR __table2 =
    FILTER ( __table1, [__trend] = "No Trend" )
VAR __table3 =
    GENERATE (
        SELECTCOLUMNS ( __table2, "__Product", [Products] ),
        RELATEDTABLE ( PMS_COMPLAINT )
    )
RETURN
    COUNTAX ( __table3, [PMSReportability] ) + 0

[Icey]

Hi @Anonymous ,

Try this:

NoRep =
VAR t =
    GENERATE (
        SELECTCOLUMNS (
            SUMMARIZE (
                ALL ( PMS_COMPLAINT ),
                [Products],
                "__p-value", CALCULATE ( [p-value], ALL ( PMS_COMPLAINT[PMSReportability] ) ),
                "__TAU", CALCULATE ( [TAU 1], ALL ( PMS_COMPLAINT[PMSReportability] ) ),
                "__trend", CALCULATE (
                    [__trend],
                    FILTER ( ALL ( PMS_COMPLAINT[PMSReportability] ), [__trend] = "No Trend" )
                )
            ),
            "_Product", [Products]
        ),
        RELATEDTABLE ( PMS_COMPLAINT )
    )
RETURN
    COUNTAX ( t, [PMSReportability] ) + 0

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Firstly, thanks for the feedback, Icey

The data set is not filtering when selecting No Trend, and or up or down. just gives me the over total value for the data.

[Icey]

Hi @Anonymous ,

If you don't mind, please give me some data sample or create an example PBIX file.

Best Regards,

Icey

[Anonymous]

Hi Icey

Did you get the sample file

[Anonymous]

Hi Icey

Did you manage to look at the measure as to why is not filtering?

[Icey · Accepted solution]

Hi @Anonymous ,

Sorry to reply late. I find that if you change your UR measure like below, it will change.

UR =
VAR t =
    GENERATE (
        SELECTCOLUMNS (
            SUMMARIZE (
                ALL ( Dim_PARTS ),
                [Product],
                "__p-value", CALCULATE ( [p-value], ALL ( Dim_Products[Reportability] ) ),
                "__TAU", CALCULATE ( [TAU 1], ALL ( Dim_Products[Reportability] ) ),
                "__trend", CALCULATE ( [__trend], ALL ( Dim_Products[Reportability] ) )
            ),
            "_Product", [Product]
        ),
        RELATEDTABLE ( Dim_Products )
    )
VAR t2 =
    FILTER ( t, [__trend] = "Up" )
RETURN
    COUNTAX ( t2, [PMSReportability] ) + 0

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Icey

If you add your formula to the messure for down ward trend by changing up to down in the measure it give you blank when the downward trending item is 876..as shown in the above matrix table.

[Anonymous]

https://www.dropbox.com/s/ywmhup95dtpm6cd/Test_file.pbix?dl=0

Look at the donut chart and measure UR when you select upward trending the value does not change remains at 876