DAX Query taking longer time

[Anonymous]

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])))

@v-juanli-msft @PBICommunity @DAXY 

[mahoneypat]

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

[lbendlin]

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/