cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
murtaaz763 Frequent Visitor
Frequent Visitor

DAX is very slow but corresponding MDX is very fast

My MDX is running in 2 seconds but the DAX in Power BI Report is taking more than 40 seconds when queried on Tabular Model. Is there any open issue in Power BI?

8 REPLIES 8
Super User
Super User

Re: DAX is very slow but corresponding MDX is very fast

There is no issue. The only issue might be that you've created DAX against best practices and you are killing the engine. My advice would be to learn how to write proper DAX.

 

Best

Darek

murtaaz763 Frequent Visitor
Frequent Visitor

Re: DAX is very slow but corresponding MDX is very fast

Hi Darlove,

 

thanks for your time and reply.

 

I have only dragged measures in Power BI and it is loading very slow. i took the same measures and converted them into MDX and fired on Tabular. It loaded way too fast.

 

Do you think of any scenario/reason behind this?

Super User
Super User

Re: DAX is very slow but corresponding MDX is very fast

Show me the definition of the troublesome measure(s). Please, format the measure(s) on www.daxformatter.com.

 

Best

Darek

murtaaz763 Frequent Visitor
Frequent Visitor

Re: DAX is very slow but corresponding MDX is very fast

I have renamed the measures but kept structured same for security purposes.

 

DAX taken from Power BI desktop which is taking 40 seconds:

// DAX Query
DEFINE
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS ( VALUES ( 'Segment'[Specialization] ) ),
            AND (
                'Segment'[Specialization] IN { "ISV" },
                NOT ( ISBLANK ( 'Segment'[Specialization] ) )
            )
        )
    VAR __DS0FilterTable2 =
        TREATAS ( { "Billed Revenue" }, 'Metric'[Metrics] )
EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    __DS0FilterTable2,
    "Total_Growth_Plan_YoY____", IGNORE ( 'Billed Revenue'[Total Growth Plan YoY (%)] )
)

Corresponding MDX which is returning result in 2 seconds:

 

SELECT

 {[Measures].[Total Growth Plan YoY (%)]}
 ON COLUMNS
 FROM [Model]
WHERE
(
[Segment].[Specialization].&[SV]
,[Metric].[Metrics].&[Billed Revenue]
)

 

Measure definition:

 

Total Growth Plan YoY (%):=

IF (
    ISFILTERED ( 'CoSell Prioritized'[Is Co Sell Prioritized] ),
    IF (
        MAX ( 'CoSell Prioritized'[Is Co Sell Prioritized] ) = "Yes",
        [CoSell Growth Plan YoY (%)],
        [ Other Growth Plan YoY (%)]
    ),
    [Total Growth Plan YoY Intermediate]
)

 

 

My observation is, Power is not considering 'Is Filtered' check in Measure definition. It is querying for all Base measures present in the measure in query than filtering out result. 

Super User
Super User

Re: DAX is very slow but corresponding MDX is very fast

First of all, try this:

 

Total Growth Plan YoY (%):=
var __CoSell_Growth_Plan_YoY_Perc = [CoSell Growth Plan YoY (%)]
var __Other_Growth_Plan_YoY_Perc = [Other Growth Plan YoY (%)]
var __Total_Growth_Plan_YoY_Intermediate = [Total Growth Plan YoY Intermediate]
var __Is_Co_Sell_Prioritized_HasDirectFilter =
	ISFILTERED ( 'CoSell Prioritized'[Is Co Sell Prioritized] )
var __Max_Is_Co_Sell_Prioritized_IsYes =
	MAX ( 'CoSell Prioritized'[Is Co Sell Prioritized] ) = "Yes"
return
	IF (
	    __Is_Co_Sell_Prioritized_HasDirectFilter,
	    IF (
	        __Max_Is_Co_Sell_Prioritized_IsYes,
	        __CoSell_Growth_Plan_YoY_Perc,
	        __Other_Growth_Plan_YoY_Perc
	    ),
	    __Total_Growth_Plan_YoY_Intermediate
	)

This measure, though, depends on 3 other measures which should be optimized. I'd time them in DAX Studio and see where the bottlenecks are. Also, using variables may remove the need to calculate all the constituent measures because the engine only calculates the measures that are used based on the logic of the code of the outer measure.

 

It's not immediately obvious where the problem is but I'm 99.99999% sure it's not the visual itself. It's the measure(s) you use.

 

Best

Darek

murtaaz763 Frequent Visitor
Frequent Visitor

Re: DAX is very slow but corresponding MDX is very fast

Thanks for the update again. This actually looks like has gained some performance. But how come it is better than the original measure definition? In original definition, i am calling only the measure based on a particular condition. But in the approach you suggested, i will be calling all the measures anyway and assigining them in variable regardless if they are needed or not based on a particular filter selected. Can you explain this a little bit.
murtaaz763 Frequent Visitor
Frequent Visitor

Re: DAX is very slow but corresponding MDX is very fast

Thanks for the update again. This actually looks like has gained some performance. But how come it is better than the original measure definition? In original definition, i am calling only the measure based on a particular condition. But in the approach you suggested, i will be calling all the measures anyway and assigining them in variable regardless if they are needed or not based on a particular filter selected. Can you explain this a little bit.

Super User
Super User

Re: DAX is very slow but corresponding MDX is very fast

Of course, you're wrong. Variables in DAX are calculated only when they are needed.

 

Best

Darek

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 112 members 1,468 guests
Please welcome our newest community members: