cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gooranga1
Impactful Individual
Impactful Individual

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-polly-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
Super User
Super User

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
Impactful Individual
Impactful Individual

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
Super User
Super User

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!