Trouble with simple calculation within calculate and sumx

[KBD]

I am having trouble performing a simple calc as follows:

tstDivide = calculate(  sumx( goalAssist,  goalAssist[minutes played] / goalAssist[minutes played]  ) , FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) ) 

 I would expect this to return one all day every day but it does not.

This also does not work:

tstMultiply = calculate(  sumx( goalAssist,  goalAssist[minutes played] * goalAssist[minutes played]  ) , FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )   

But if I multiple by a constant, say 10,  it works fine. So I believe I have the syntax and paranthese right.

The Power BI viz testGoalAssistII sheet rolling II shows what is going on.
Can be found here: https://drive.google.com/file/d/1jXbo3OBAaicnLI2lmz4I2pnLUiUjJM2p/view?usp=drive_link

The filter

What I need to do is a bit more complicated.  I took me a while to strip it down to this issue.

The more complicated version is here: Re: calc a rolling rate - Microsoft Fabric Community

Solving this simple issue would be a big help.

Thanks for your attention to this matter

KBD

[V-yubandi-msft]

Hi @KBD ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

[V-yubandi-msft]

Hi @KBD ,

Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

Thank You.

[V-yubandi-msft]

Hi @KBD ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @KBD ,

Thank you for sharing your query in the Fabric Community. Your DAX logic appears correct syntactically, but the issue likely arises from how division or multiplication behaves when filtered value such as  are 0 or absent in some contexts.

A few tips to refine the measure
1. Use the DIVIDE() function instead of direct division, as it handles divide by zero cases more gracefully.
2. Wrap measures with IF(ISBLANK(...), 0, ...) to control behaviour during aggregation.

I hope this helps...

[bhanu_gautam]

@KBD , Try using

tstDivide = CALCULATE(
SUMX(
goalAssist,
DIVIDE(goalAssist[minutes played], goalAssist[minutes played], 1)
),
FILTER(
ALLSELECTED(goalAssist[yyyymm]),
goalAssist[yyyymm] <= MAX(goalAssist[yyyymm])
)
)

tstMultiply = CALCULATE(
SUMX(
goalAssist,
goalAssist[minutes played] * goalAssist[minutes played]
),
FILTER(
ALLSELECTED(goalAssist[yyyymm]),
goalAssist[yyyymm] <= MAX(goalAssist[yyyymm])
)
)

[KBD]

Gautam:

Thanks for your reply.

I believe your second suggestion above is what I alrerady have.  

The above  formulas are stripped down versions,  the real formual looks more like this:

effectiveRate OverPeriods = calculate(  sumx( goalAssist,  ( goalAssist[goals] +  (goalAssist[assists]  *  .5)) * 60 /  goalAssist[minutes played]   ) , FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )  

I rewrote and stripped down the above and found it was the divide by or the multiple by section that was misbehaving.   Any suggestion on rewriting the above.

The Viz can be found here:  Can be found here: https://drive.google.com/file/d/1jXbo3OBAaicnLI2lmz4I2pnLUiUjJM2p/view?usp=drive_link

Thanks

KD