Forum Discussion
Incorrect result when dividing with measure
Hey everyone,
I have devised a DAX function which basically checks if some conditions are satisified or not, if it satisifies the condition then it divides the column with a dynamically changing measure( which is multiplication of no. of working days in a month with a constant).
Now if I divide the column with no. of working days in a month with a constant, manually, it gives me correct output, but with measure which changes dynamically it gives me wrong output.
My colleague checked every logica and function I used, nothing's wrong. Even for small value like 1.3 I'm getting 708.6. Everything works fine except the part where I am dividing with measure.
I have no idea what's wrong. Thought you guys might know what's wrong.
Thanks.
Hi Anonymous ,
So the key point of this issue is to calculated the working days dynamically right?
Suppose you have a calendar date table and use it as slicer, now I select 2020 August, maybe you need measure like this:
Measure = var _workingdays = COUNTX( FILTER( ALLSELECTED('Calendar'), WEEKDAY('Calendar'[Date],2) <> 6 && WEEKDAY('Calendar'[Date],2) <> 7 ), 'Calendar'[Date] ) return IF( SELECTEDVALUE('Table'[LSA]) = "LSA1" && SELECTEDVALUE('Table'[Rate Type]) = "Onshore", CALCULATE( SUM('Table'[Hours Delivered]), FILTER( ALL('Table'), 'Table'[LSA] = "LSA1" && 'Table'[Rate Type] = "Onshore" ) ) / (6.24 * _workingdays) )The variable in the formula will change when you select different date in the slicer and the total will change dynamically at the same time.
As for [hours delivered], I suppose it to calculate in the specific condition, for example, total of LSA1 when rate type = onshore.
Attached my sample file in the below, hopes to help you.
By the way, I would suggest you to create measures instead of calculated columns like your previous formula and using switch() function which would have better performance.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- daxer-almighty
Solution Sage
Hi Anonymous
When you are trying to calculate with a measure in a fact table row-by-row, you have to know the details about row context and how it interacts with measures (the so-called "context transition"). Because you most likely don't know it (as I can gather from your posts), you're getting wrong results.
On the other hand, using a measure to calculate columns in a big fact table is a big no-no. You should never do it. Because of context transition the calculation will be very slow (it can even throw out an out-of-mem error, which has been the topic of quite a few posts seen on this forum). What's more, calculated columns should be moved back to Power Query as this is the tool for rapid data munging. DAX is for data analysis. Worse, if you do it in DAX (especially on big fact tables) the compression will be extremely poor and hence the model will be bloated, DAX will be slower than it could be.
Hence my advice to you: please move the calculation to where it belongs - Power Query - and you'll be thankful that you did.
One last thing... There is a much better construct in DAX to express your logic: SWITCH. The IF is good when you have at most 2 conditions.
- mahoneypat
Microsoft Employee
Please share your DAX expression, along with example input data and expected result. You could use variables to troubleshoot to see what is being returned for the day count part.
Regards,
Pat
Hi mahoneypat ,
The issue-
Data available -
Fact table has more than 70columns and 200000rows out of which the table I need to create in BI is concerened with only Service Period, Rate Type, LSA , Type of work, charge type and no. of hours delieverd.Example-
LSA Service Period Rate Type Hours Delieverd Type of work Charge type LSA1 01-07-2014 Onshore 1 Service for assistance Fixed LSA2 01-12-2015 Onshore 0.5 Project Fixed LSA3 01-06-2019 Offshore 2 Project Variable LSA2 01-09-2020 Onshore 12 Enhancement Variable LSA4 01-06-2019 Offshore 2 Maintenance Fixed
Desired Output-
I need to create a table which calculates the no. of hours delivered based on Rate Type, Charge Type, Type of Work on the basis of Month and Year and LSA.
Example- Where the data changes dynamically with month and yearLSA FTE of Rate Type Onshore FTE of Rate Type Offshore FTE of Rate Type Blended FTE for Application Enhancement FTE of work type Variable LSA1 25.3 77.8 7.1 0.0 4.9 LSA2 26.4 1.6 23.8 2.6 61.1 LSA3 4.0 0.9 68.6 0.0 0.0 LSA4 1.3 46.2 20.9 0.0 23.7 Now these values are calculated using-
No. of hours of work delivered in a month(with conditions) divided by total no. of available working hours in a month. which is different for different LSAs. Example-
Total working hours in a month = no. of working hours in a dayfixed but different for Onshore and Offshore) * no. of working days in a month(changes dynamically)
LSA1(working hours in a month for LSA1) = 6.24 * 21(131.04) OR,
LSA2(working hours in a month for LSA2) = 6.52* 21(136.92)What has be done so far-
Completed the table and logic except the final calculation part(wrong values I got).
DAX I have created -FTE of Rate Type Onshore = ( IF ( [Rate Type] == "Paid", ( IF ( [office] == "Farm", [Hours delivered] / 157.08 , ( IF ( [office] == "Boult", [Hours delivered] /127.68 , ( IF ( [office] == "Global", [Hours delivered] / 136.92, ( IF ( [office] == "GOTS", [Hours delivered] / 131.04, ( IF ( [office] == "Daxnd", [Hours delivered] / 136.92, ( IF ( [office] == "UK", [Hours delivered] / 131.04, ( IF ( [office] == "UTS", [Hours delivered] / 131.04, ( IF ( [office] == "ANZ", [Hours delivered] / 149.94, 0 ) )))))))))))))))))131.04(working hours in a month) = 6.08(working hours in a day) * 21(August 2020->21 working days)
Working hours in a month will change dynamically with month.
Thing where I'm stuck-
I have devised logic for calculating no. of working days (a measure in fact table), using calendar table, but when I'm using it to divide it with sum of professional hours delivered. It does not produce an expected output.
The code I'm using is given above.
Everything is working perfectly fine sperately, but when I'm using measure for division the output produced is wrong.Help would be much appreciated I'm stuck here for last 2 days(just a beginner).
Thanks in advance for helping.
v-yingjlCommunity Support
Hi Anonymous ,
Based on your description, seems not certain how did it calulate, for example LSA1, 6.08 is the working hours per day, 21 is the working days in August 2014, but the service period of LSA1 I saw in your sample data is 2014/7/1. Did you want to calculate the total hours of the next month working days or anything else?
Could you please consider sharing more details about this calculation or a simple sample file based on the data picture and the sample expected output for further discussion? It could let us help you better to solve this issue.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
