Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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?
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.
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.
Of course, you're wrong. Variables in DAX are calculated only when they are needed.
Best
Darek
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |