Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Division by Measure not working

Hi,

 

I have a table which contains details related to meetings including start, end times etc. From that table I use calculate columns to determine meeting length. I am now trying to determine utilization % based on the number of days selected in a time slicer.

 

For example, I have a meeting on 11th Dec that lasts 8 hours. The utilization (calculated as % of an 8 hour working day) is calculated as such:

 

    Util8hrDay = (([LengthHours] /8 ) ) * 100.0
 

The value for the above example is therefore 100% (which is CORRECT).

 

 

The number of days selected in the time slicer is calculated as a measure:
   
    SelectedDays = (DATEDIFF(FIRSTDATE(DateTable[Date]),LASTDATE(DateTable[Date]),DAY)) + 1

 

And the DAX for the calculation is: 

   
    Util8hrDayPerDay = Meetings[Util8hrDay] / DateTable[SelectedDays]
 
(DateTable is my time slicer table and holds a many:1 relationship with the meeting start time in the Meetings table.)
 
If I select a date range of both the 11th and 12th (lets assume there is no meeting on the 12th), the number of selected days is returned as 2 (which is CORRECT). However, if I use that as a divisor as below, the calculation seems to ignore it and returns a utilization of 100% still even though it should now be 50%.
 
If I change the DAX above to explicity put a value of 2 as the divisor it works - so why is it not using the measure DateTable[SelectedDays] correctly?
 
-Dave
 
 
 
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Possible to post some data?  Usually get a much better response if people have something to work with!

     

    Also, probably want to be using the DIVIDE function in case of errors.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Is there a way to send an example pbix file?

       

      If not, just use this and the DAX in the post above:

       

      meetingID   |           startDate          |   endDate

      ======================================

      123456        | 10/11/2018 09:00:00  |  10/11/2018 17:00:00

      654321        | 13/11/2018 14:00:00  |  13/11/2018 16:00:00

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        OK - so have been doing some more digging and think I know where the problem lies, but just cannot resolve it.  As you can see from the attached image, I have created a date lookup table (DateTable) and my sample data is in Table1.

         

        SelectedDays is the result of the DAX expression, the range of which is controlled by the displayed slicer: 

        SelectedDays = COUNTROWS ( ALLSELECTED ( 'DateTable'[Date] ) )
         
        This is returning the number of days that has been selected by the slicer and I am showing it;s value in the card and also in the last column of the table.
         
        Column1 is the result of:   Table1[Util8hrDay] / DateTable[SelectedDays]
         
        Column2 is the result of: DateTable[SelectedDays]

         

        For the first row in the table, Column1 should return a value of 25 (i.e. 100.00 / 4) but instead returns 0.03.

        If you look at the value of Column2 (which is a new column set to the value of the Measure of days selected), this shows a value of 2922 instead of 4.  In fact, 2922 is the total number of days in the DateTable. This is why the division is failing.

         

        But why does it return the unfiltered value of 2922 instead of 4 when used in the calculation or displayed as a separate column - why is this negating the time slicer filter?

         

        Thanks,

        Dave