cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
murtaaz763
Regular 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
Anonymous
Not applicable

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

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?

Anonymous
Not applicable

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

 

Best

Darek

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. 

Anonymous
Not applicable

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

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.

Anonymous
Not applicable

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

 

Best

Darek

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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors