Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Hi @gooranga1 ,
Follow best practices for using import versus DirectQuery:
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
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
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.
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).
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |