Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors