Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have a report connected to a dataverse table via direct query. Right now the dataverse table only has 40 records so not many records at all.
A detail page in the Power BI report displays 33 text data fields from the same selected record. The visuals are grouped based on their information and displayed in a matrix visual (used Guy in a Cube Adam's suggestion to use matrix visual and that has helped to decrease load time):
Matrix Visual 1 - contains 3 text fields
Matrix Visual 2 - contains 3 text fields
Matrix Visual 3 - contains 4 text fields
Matrix Visual 4 - contains 4 text fields
Matrix Visual 5 - contains 4 text fields
Matrix Visual 6 - contains 3 text fields
Matrix Visual 7 - contains 7 text fields
Matrix Visual 8 - contains 2 text fields
Matrix Visual 9 - contains 3 text fields
The text fields are all set to Summarization=Don't summarize, Format=Text, Data Type=Text, and Data category=Uncategorized. The visual pulls the 'First' value for the record and displays it in the matrix visual. Running performance analyzer and finding that there are 2 components that are very high in time, DAX query and direct query.
1) Is there any other visual that is more time efficient that could be used in place of matrix to display the text fields?
2) If connecting to dataverse table in direct query, why does a DAX component appear and can it be removed?
3) Any suggestions on how to decrease the very high DAX/direct query time? Here is the performance analyzer query from one of the matrix visuals - it seems to be executing a lot of non-applicable summarize and minimum calculations on the text fields:
DEFINE VAR __DS0FilterTable =
TREATAS({"Test Record #26"}, 'jj55a_details'[jj55a_name])
EVALUATE
SUMMARIZECOLUMNS(
__DS0FilterTable,
"Minjj55a_area", IGNORE(CALCULATE(MIN('jj55a_details'[jj55a_area]))),
"Minjj55a_state", IGNORE(CALCULATE(MIN('jj55a_details'[jj55a_state]))),
"Minjj55a_location", IGNORE(CALCULATE(MIN('jj55a_details'[jj55a_location]))),
"Minjj55a_address", IGNORE(CALCULATE(MIN('jj55a_details'[jj55a_address])))
)
@luvagoldenk9 Look at the query plan in DAX Studio and check how many records are returned in each step (second column). Might be a cartesian product somewhere that you can avoid/mitigate by rewriting your measure queries.
Also check with the data source owners if they would be willing to do some index tuning for the queries Power BI is producing.
And lastly - use fewer visuals on the page. Visuals are rendered in groups of five, so if you have more than that a lot of your visuals will waste time waiting (the "Other" category).
@lbendlin Thank you for the input. Unfortunately I don't have access to DAX studio. For cartesian theory ... would this still apply as there is only 1 Dataverse table that data is being fetched from.
In reference to using fewer visuals ... each record has about 40 fields. The user wants to see 36 of the fields in a 'pretty' view vs in a table/scrolling. I've grouped the fields on the report page using 10 matrix visuals. Do you know if there is a better visual?
Thank you again!
What do you mean "don't have access to DAX Studio" ? I don't think your IT department can prevent you from installing that, or?
There's a thing called Small Multiples but it is not applicable to matrix visuals. Maybe find a different visual in the market place?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |