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
gooranga1
Power Participant
Power Participant

Slow minx/max measure

I am doing something horrible with a tabular measure that is using a huge amount of formula engine.

 

There is a table that contains all warehouse picks by date and time picker warehouse  and is about 100 million rows. There are 2 measures for first pick time and last pick time which looks like below. I have a time dimension.

 

Earliest Pick Time:=
CALCULATE ( MINX ( 'Time', 'Time'[Time Value] ), Items )

 

 and the same by Max for latest time.

I run a DAX query using this measure that returns 600,000 rows which is occasionaly timing out but always takes a long time to run.

 

EVALUATE
SUMMARIZECOLUMNS (
Pickers[User Id],
Company[Company Id],
'Date'[Date],
Warehouses[Warehouse Id],
'Warehouse Events'[Event Group Id],
FILTER ( all('Date'[Date]), 'Date'[Date] >= DATE ( YEAR ( TODAY () ) - 3, 01, 01 ) ),
"Items", [Total Items],
"Actions", [Total Actions],
"Earliest Pick", [Earliest Pick Time],
"Latest Pick", [Latest Pick Time]
)

 

when I run this it is exclusively run on the formula engine which is why it is so slow 204 seconds. I am sure someone out here will have a heart attack at this measure and point me in the right direction to get it running from the storage engine and not taking so long to run.

 

5 REPLIES 5
v-rongtiep-msft
Community Support
Community Support

Hi @gooranga1 ,

Follow best practices for using import versus DirectQuery:

  • You should import data to Power BI wherever possible. Importing takes advantage of the high performance query engine of Power BI, and provides a highly interactive and fully featured experience.
  • If your goals can't be met by importing data, consider using DirectQuery. For example, if the data is changing frequently and reports must reflect the latest data, DirectQuery may be best. However, using DirectQuery is only feasible when the underlying data source can provide interactive queries, less than 5 seconds for the typical aggregate query, and can handle the query load that will be generated. Additionally, the list of limitations for the use of DirectQuery should be considered carefully.

Please choose the right one.

 

Best Regards

Community Support Team _ polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@gooranga1 Why are you using this synthax for the Earliest Pick Time?

CALCULATE ( MINX ( 'Time', 'Time'[Time Value] ), Items )

Don't you have the date in the table that contains all the Warehouse pick that you can just do
MIN( Table[Pick date] )?

 
Also, why do you have to use a table filter for Items?

Without a Data model schema, we cannot help you more

m3tr01d
Continued Contributor
Continued Contributor

hello @gooranga1 

1) Is the table loaded using Import or Direct query?

2) How many columns do the Fact table have?

3) Did you try running the query for each measure so you can pin point the one that take more time?

4) What is the code of Total Item and Total Actions?
5) What are all the tables and the relationships on the model with the cardinality?

Thanks

gooranga1
Power Participant
Power Participant

hi @PaulDBrown  yes it's a query I am using to load data into power bi. I think you are right that this needs to be aggregated first in the data warehouse. I just didn't know whether I was missing something obvious.

PaulDBrown
Community Champion
Community Champion

You are creating a table, not a measure. Working with 100 million rows. Wow! I'm not surprised it is laborious.

Sometimes it's sensible to in fact create aggregated tables to speed things up. The run time for tables happens when the model is loaded, but the benefit is that working on these tables should speed up the rendering of meaaures (since the number of rows is reduced). 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.