Forum Discussion
Helper IIIDividing measure returning false results
ello everyone,
I have two measures which I didvided by each other, and the result Power BI gives me is not the same as when I calculate them manually. Here are the measures (please know that even though I have all date columns as Type Date/Time, I still get an error that I can't use it Type Text with Date and that I should use the FORMAT function):
rounds measure = SUM('Table'[rounds])
rounds =
seconds measure = SUM('Table'[seconds])
seconds =
seconds/rounds = DIVIDE ([seconds],[rounds])
(It's the same whether I use DIVIDE() or simply /)
There's a column called Game Type.
Here is what I get from diving them, even though if I manually calculate seconds by rounds I get a different answer:
| Game Type | rounds | seconds | seconds/rounds | |||
| Game 1 | 3254 | 14558.57 | 4.28 | |||
| Game 2 | 609 | 2187.99 | 3.50 | |||
| Game 3 | 718 | 2230.60 | 3.05 |
Hello Adescrit,
thanks a lot! This gives me the correct result (the same when I divide them manually).
I also implemented the advice given me above about not comparing Dates to strings and using || along with your solution, so now I have this:CALCULATE (DIVIDE([rounds], 'seconds]),FILTER('Dates','Dates'[Date] = ENDOFMONTH('Dates'[Date])))
and it workds perfectly, thank you againHello again, I find the solution to the aboce question 🙂
https://sqlkover.com/calculate-a-semi-additive-average-in-dax/
15 Replies
daXtremeSolution Sage
If you have to write such ugly code, it means you're not doing it right (sorry!). Converting dates into strings and then comparing them to strings (using || on top of that) is a BIG NO-NO. This code is hard to read, hard to maintain and prone to errors. If you want to compare a real date to a date literal, here's how you create a date literal:
Either:
DATE( year, month, day )where the arguments are integers, or
dt"yyyy-MM-dd"If you need to check if a value is in a set of values, use the IN operator instead of "||" and the anonymous table generating construct:
{val1, val2, val3,...}Please revise your setup first.
S3Hello daXtreme,
Thank you for letting me know that my code is very wrong, I just wanted to calculate the value of the last day of each month, because in the dataset they put the total in each day, and not the difference, so in order to calculate the total of January, I have to choose only January 31st.
I didn't really understand how to implement what you've suggeted, is each a measure on its own? I've tried the first one but Power BI told me that "day" is not accepted. If you have time and can let me know how to implement what you've suggetsed I would be very grateful to learn.
I tried now this measure instead:Rounds=
CALCULATE ([rounds],FILTER('Dates', 'Dates'[Date] = ENDOFMONTH('Dates'[Date])))
and the same for seconds, and it works, as in it gives me the same value at the end of each month without comparing dates to strings and using ||. So the ugly part is over, however, the calculation of seconds/rounds is still giving the same false result..- daXtreme
By the way... A more efficient measure is this:
Rounds = var EndOfMonthDate = ENDOFMONTH('Dates'[Date] ) var Output = CALCULATE ( [rounds], KEEPFILTERS( 'Dates'[Date] = EndOfMonthDate ) ) return OutputThat's because it uses KEEPFILTERS.
- S3
I have implemented your tips with the solution below from Adescrit and I wrote this:
CALCULATE (DIVIDE([rounds], 'seconds]),FILTER('Dates','Dates'[Date] = ENDOFMONTH('Dates'[Date])))
and it works, thanks a lot again
- Adescrit
Impactful Individual
Hi S3 ,
How about trying the following:
seconds/rounds = CALCULATE ( DIVIDE([seconds measure], [rounds measure]), FILTER('Dates', (FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.01.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "28.02.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.03.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.04.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.05.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.06.22" || FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.07.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.08.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.09.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.10.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "30.11.22"|| FORMAT('Dates'[Date], "dd.mm.yy" ) = "31.12.22")))Does this give you the correct result?
- S3
Hello Adescrit,
thanks a lot! This gives me the correct result (the same when I divide them manually).
I also implemented the advice given me above about not comparing Dates to strings and using || along with your solution, so now I have this:CALCULATE (DIVIDE([rounds], 'seconds]),FILTER('Dates','Dates'[Date] = ENDOFMONTH('Dates'[Date])))
and it workds perfectly, thank you again
