topic Re: DAX formula Not working in DAX Commands and Tips

DAX formula Not working

sixathur — Thu, 06 Mar 2025 06:04:17 GMT

Hi,

I am trying to create a DAX measure where an existing measure named "TOTAL VISITS", if total visits is more than 58, multiply the difference between TOTAL VISITS and 58 by 15. For example: Total visits = 60, 60-58 =2, then 2(15) =30. The idea is do that for every week in the given dataset then total all the data that is multiplied by 2. Right now when I use IF statement or VAR statements, it's totaling ALL the visits not just visits more than 58 and multiplying that with 15. Is it because I am using a Measure (TOTAL VISITS) instead of an actual column?

Thank you.

Re: DAX formula Not working

mdaatifraza5556 — Thu, 06 Mar 2025 10:39:31 GMT

Hi sixathur

Can u please try the below DAX.

For_Visits_more_than_58 =
VAR Visits_Above_58 =
SUMX(
VALUES('Table'[Week]),
MAX( [TOTAL VISITS] - 58, 0 ) * 15
)
RETURN
Visits_Above_58

If you find this solution usefull then accept it as solution.

Re: DAX formula Not working

johnbasha33 — Thu, 06 Mar 2025 07:06:03 GMT

Hi sixathur
You're on the right track, and the issue likely stems from how measures work in Power BI. Since TOTAL VISITS is a measure, it dynamically evaluates based on the current filter context, which means you need to carefully control its aggregation to ensure only visits above 58 are counted.
try this
Extra_Visit_Cost =
SUMX(
VALUES('YourTable'[Week]), -- Iterate over each week
VAR VisitCount = [TOTAL VISITS] -- Get total visits for the current week
RETURN IF(VisitCount > 58, (VisitCount - 58) * 15, 0)
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Re: DAX formula Not working

sixathur — Wed, 12 Mar 2025 00:21:39 GMT

Hi,

Thank you for your help, the formula worked but when I use the card visual for this measure, its still giving me the total of the visits*15, not the difference*15.

Re: DAX formula Not working

sixathur — Wed, 12 Mar 2025 00:37:29 GMT

Hi,

Thank you for your help, the formula worked but when I use the card visual for this measure, its still giving me the total of the visits*15, not the difference*15.

Re: DAX formula Not working

mdaatifraza5556 — Wed, 12 Mar 2025 03:50:00 GMT

Hi sixathur

Please try my above DAX.
belowis the snapshot which i have tried

For_Visits_more_than_58 =

VAR Visits_Above_58 =

SUMX(

VALUES('Date'[weekno]),

MAX(

[TotalVisits] - 58,

) * 15

)

RETURN

Visits_Above_58

--------------------------------------------
If total visits is less than 60 then it shows 0

Please accept it as a solution if you got your answer.

Re: DAX formula Not working

sixathur — Wed, 12 Mar 2025 16:55:22 GMT

Hi,

I do not have a week number slicer, my slicer is based on the start of the week date.

Re: DAX formula Not working

tamerj1 — Wed, 12 Mar 2025 18:20:42 GMT

sixathur

What is the dax of TotalVisits measure?

Re: DAX formula Not working

sixathur — Wed, 12 Mar 2025 19:37:23 GMT

CALCULATE(countrows(visits))

Re: DAX formula Not working

Anonymous — Fri, 02 May 2025 04:26:22 GMT

Hi sixathur,

Thank you for reaching out in Microsoft Community Forum.

Thank you tamerj1 mdaatifraza5556 johnbasha33 for the helpful response.

Please follow below steps to resolve the error;

1.Adjust the [TotalVisits] measure to respect the weekly context (start of the week date). Use this DAX:

TotalVisits =
CALCULATE(
COUNTROWS(visits),
ALLEXCEPT('Date', 'Date'[WeekStartDate])
)

2.Extra Visit Cost Measure:

For_Visits_More_Than_58 =
SUMX(
VALUES('Date'[WeekStartDate]), -- Iterate over each week start date
VAR WeeklyVisits = [TotalVisits]
VAR ExtraVisits = WeeklyVisits - 58
RETURN IF(ExtraVisits > 0, ExtraVisits * 15, 0)
)

3. Please use visuals like below

->Use a Matrix visual to verify the logic per week.

->The Card visual will show the aggregated total, but check per week first in Matrix.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

Re: DAX formula Not working

Anonymous — Wed, 07 May 2025 09:06:54 GMT

Hi sixathur,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

Re: DAX formula Not working

Anonymous — Sun, 11 May 2025 08:39:57 GMT

Hi sixathur,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

Re: DAX formula Not working

Anonymous — Thu, 15 May 2025 16:41:56 GMT

Hi sixathur,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you,
Pavan.