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.
Hi ,
Could anyone please help me on how to increase the perfomance of these two queries as it is taking longer than 4-12 mins to execute the query and show the data in Matrix . Also the connection is Direct Query
BI_WorkerCount weighted by FTE per BI_Department =
VAR __CATEGORY_VALUES = VALUES('FVHBISM_FVHBISRT_MATERIALIZED'[BI_Department])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(
[BI_WorkerCount]
* SUM('FVHBISM_FVHBISRT_MATERIALIZED'[FTE])
)
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUM('FVHBISM_FVHBISRT_MATERIALIZED'[FTE]))
)
)
BI_Registrations =
CALCULATE
(
DISTINCTCOUNT(FVHBISM_FVHBISRT_MATERIALIZED[ABSENCEREGISTRATIONID]),
FILTER
( FVHBISM_FVHBISRT_MATERIALIZED, FVHBISicknessMeasurement_FVHBISRT_MATERIALIZED[SICKNESSREGVALIDFROM] >=FIRSTDATE(FVHBISM_FVHBISRT_MATERIALIZED[FROMDATE]) &&FVHBISM_FVHBISRT_MATERIALIZED[SICKNESSREGVALIDFROM]<=LASTDATE(FVHBISM_FVHBISRT_MATERIALIZED[FROMDATE])))
For your first one, I haven't seen KEEPFILTERS() used that way, and don't think it is doing anything. VALUES should be honoring any relevant filters anyway.
In any case, you have some redundancy in your measure. I would suggest an approach like this for your first measure:
New First Measure =
VAR __summarytable =
ADDCOLUMNS (
VALUES ( 'FVHBISM_FVHBISRT_MATERIALIZED'[BI_Department] ),
"@WorkerCount", [BI_WorkerCount],
"@FTEsum", CALCULATE ( SUM ( 'FVHBISM_FVHBISRT_MATERIALIZED'[FTE] ) )
)
RETURN
DIVIDE (
SUMX ( __summarytable, [@WorkerCount] * [@FTEsum] ),
SUMX ( __summarytable, [@FTEsum] )
)
For your second one, I would try an expression like this. I suspect your slow refresh was the first one. Have you tried them separately to see that both are slow? Also, in the inner filter you reference a column from another table. Just making sure that is correct.
BI_Registrations =
VAR __firstdate =
MIN ( FVHBISM_FVHBISRT_MATERIALIZED[FROMDATE] )
VAR __lastdate =
MAX ( FVHBISM_FVHBISRT_MATERIALIZED[FROMDATE] )
RETURN
CALCULATE (
DISTINCTCOUNT ( FVHBISM_FVHBISRT_MATERIALIZED[ABSENCEREGISTRATIONID] ),
FILTER (
FILTER (
FVHBISM_FVHBISRT_MATERIALIZED,
FVHBISicknessMeasurement_FVHBISRT_MATERIALIZED[SICKNESSREGVALIDFROM] >= __firstdate
),
FVHBISM_FVHBISRT_MATERIALIZED[SICKNESSREGVALIDFROM] <= __lastdate
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Open DAX Studio (install it if you haven't yet).
Paste the query there, enable Query Plan display and Server Timings, run your query (with clear cache), and then study the query plan for large row counts. Once the culprit is identified you can decide how to rewrite your DAX to make that part faster.
At SQLBI they have a fantastic series on how to use DAX Studio
https://www.sqlbi.com/tv/query-performance-tuning-in-dax-studio/
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |