Incorrect result when dividing with measure

[Anonymous]

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.

[daxer-almighty]

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]

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

[Anonymous]

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 year

LSA	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-yingjl]

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.

[Anonymous]

Hey @v-yingjl 
The data set is too large that's why I used 5 columns as example.
The data set has data from January 2014 to September 2020 for each and every month of the year. 

Serive period has only the date of begining of each month irrespective of data(Ex- 1/1/14;1/2/14.........,1/9/20). Service period is just used to inform what month and year is being used. But we need to calculate total no. of working days in given month and multiply it with working hours in a day to get total working hours in a month.

[v-yingjl · Accepted solution]

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.

[Anonymous]

@v-yingjl 
I have solved this problem with the help of my colleague. Like you suggested we used Measure instead of columns and it's working fine except Totals .

I'm also checking your code if it fits I'll accept it as solution. Thanks a lot for your help.

P.S.- I will post the total problem in a new thread, if you want to have a look just let me know,I'll mention you there.