cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Route217 Member
Member

DAX Measure is very slow.

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
1 ACCEPTED SOLUTION

Accepted Solutions
Icey Super Contributor
Super Contributor

Re: DAX Measure is very slow.

Hi @Route217 ,

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.

View solution in original post

8 REPLIES 8
Icey Super Contributor
Super Contributor

Re: DAX Measure is very slow.

Hi @Route217 ,

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.

Route217 Member
Member

Re: DAX Measure is very slow.

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 Super Contributor
Super Contributor

Re: DAX Measure is very slow.

Hi @Route217 ,

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

 

Best Regards,

Icey

Route217 Member
Member

Re: DAX Measure is very slow.

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
Route217 Member
Member

Re: DAX Measure is very slow.

Hi Icey

Did you get the sample file
Route217 Member
Member

Re: DAX Measure is very slow.

Hi Icey

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

Re: DAX Measure is very slow.

Hi @Route217 ,

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.

View solution in original post

Route217 Member
Member

Re: DAX Measure is very slow.

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.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 430 members 3,296 guests
Please welcome our newest community members: