Forum Discussion

TJWL's avatar
TJWL
Frequent Visitor
1 year ago
Solved

Measure fails when filter is applied

Hi community,
 
Having some issues with my directquery model using the below measure. Goal is to grab the average count of machines in the current shift per hour. What's interesting is that the measure works and displays the machine count correctly. I then place a filter onto the visual and it just spins, eventually saying its exceeded resources. It's only analyzing approximately 3,000 rows though, so this seems tough to believe. Am I introducing anything here that would not work well with a filter? The filter used has a one to many relationship with the Event table; single direction
 
MachineCount =
VAR ShiftStart = [ShiftStartTime]

 

VAR FilteredEvent = FILTER(EVENT,
    EVENT[Time] >= ShiftStart
)

 

VAR DistinctPoint = CALCULATE(
    DISTINCTCOUNT(EVENT[Machine]),
    FilteredEvent
    )

 

RETURN
AVERAGEX(
    VALUES('EVENT'[Binned Time]), -- Iterate over hour intervals
    DistinctPoint
    )
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi TJWL  - Your measure logic looks mostly fine, but the performance issue with the filter might be due to how FilteredEvent is being used in combination with AVERAGEX and CALCULATE. In DirectQuery mode, complex row context and heavy filtering can sometimes lead to inefficient queries being sent to the data source.

     

    MachineCount =
    VAR ShiftStart = [ShiftStartTime]
    RETURN
    AVERAGEX(
    VALUES('EVENT'[Binned Time]),
    CALCULATE(
    DISTINCTCOUNT('EVENT'[Machine]),
    'EVENT'[Time] >= ShiftStart
    )
    )

     

    Remove the VAR FilteredEvent variable: Instead, apply the filter directly inside the CALCULATE. This reduces intermediate steps and might help with DirectQuery performance.
    Limit the VALUES function: If 'EVENT'[Binned Time] has high cardinality, try to limit the iteration or aggregate first.

     

    Hope this helps.

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi TJWL  - Your measure logic looks mostly fine, but the performance issue with the filter might be due to how FilteredEvent is being used in combination with AVERAGEX and CALCULATE. In DirectQuery mode, complex row context and heavy filtering can sometimes lead to inefficient queries being sent to the data source.

     

    MachineCount =
    VAR ShiftStart = [ShiftStartTime]
    RETURN
    AVERAGEX(
    VALUES('EVENT'[Binned Time]),
    CALCULATE(
    DISTINCTCOUNT('EVENT'[Machine]),
    'EVENT'[Time] >= ShiftStart
    )
    )

     

    Remove the VAR FilteredEvent variable: Instead, apply the filter directly inside the CALCULATE. This reduces intermediate steps and might help with DirectQuery performance.
    Limit the VALUES function: If 'EVENT'[Binned Time] has high cardinality, try to limit the iteration or aggregate first.

     

    Hope this helps.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi TJWL 

     

    Applying FILTER to a table instead of just a specific column can be very costly especially on large tables. The variable below iterates row by row in EVENT and produces a temporary table in memory.

    VAR FilteredEvent = FILTER(EVENT,
    EVENT[Time] >= ShiftStart
)

     You can instead specify the column within the  FILTER argument

    VAR FilteredEvent = 
FILTER(
   VALUES( EVENT[Time] ), EVENT[Time]  >= ShiftStart
)

    Change your variable to this or try as rajendraongole1 has suggested. Check in DAX Studio which one is a faster alternative as per your model.

     

    Please note that 'EVENT'[Time] >= ShiftStart is written internally as 

    FILTER(
   ALL( EVENT[Time] ), EVENT[Time]  >= ShiftStart
)

    It overrides the existing filter to EVENT[Time] but VALUES keeps it.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    Hi TJWL,

    We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

    If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    Hi TJWL,

    We wanted to follow up and see if your query has been addressed. If you have discovered a solution, we would appreciate if you could share it with the community to assist others facing similar challenges.

    If you found our response helpful, please mark it as the accepted solution and give us some kudos to support the community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    1 year ago

    Hi TJWL,

    We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

    If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

    Thank you.