Measure counting working days not working

[jmsalomr]

Hi,

I'm trying to solve the following measure. I have a table with a list of dates with accounted hours for each date, and I want to calculate the ratio between the total hours for a period over the maximum, which I define as the total number of working days for these period multiplied by 8 hours each day.

This table is related with a Calendar table defined with CALENDARAUTO().

I have a Power BI file version where this works using the following measure:

Chargeability = 

DIVIDE(CALCULATE(SUM(TimeAccounted[Hours])),CALCULATE(COUNTROWS('Calendar')*8,FILTER('Calendar',WEEKDAY('Calendar'[Date],2)<=5)))

But in another Power BI file, the same measure with the same tables and relationships, doesn't work. It seems like if there is a conflict between the Date in the column and the measure.

NOTE: The difference in the rows of the Matrix is because in the second Power BI file there are less items to show.

I have checked one Power BI file and the other during so many hours and I cannot find a difference that could make this difference in the behaivour of both measures. I have created debugging tables to see what is hapenning with this measure and simply doesn't work when used with the Date of the Calendar table. It seems there is a conflict, but I cannot realize what.

Any suggestion or idea?

Please help, I spent a lot of hours and cannot understand where is the problem. What I'm doing wrong?

[daXtreme · Accepted solution]

// If in one file this measure works and it does not in another,
// then the only logical explanation is that the files are
// not equivalent despite what you say. I'm sure of this since
// there isn't any spooky hocus-pokus taking place behind 
// the scenes and differences are always explicable rationally and logically.
// However, without the files in front of one's eyes it's almost impossible
// to troubleshoot. It also is critically important that suitable
// columns are selected from suitable tables for such measures
// to work correctly. For instance, such a measure will not work
// correctly when date columns are used directly from the fact
// table instead of from the calendar...

Chargeability =
DIVIDE(
    SUM( TimeAccounted[Hours] ), // should be a measure on its own
    CALCULATE(
        COUNTROWS( 'Calendar' ) * 8,
        KEEPFILTERS(
            // Instead of this, you should have a column
            // in your calendar of the value
            // WEEKDAY( 'Calendar'[Date], 2 ) and filter
            // this column, liket so:
            // 'Calendar'[Weekday] <= 5
            WEEKDAY( 'Calendar'[Date], 2 ) <= 5
        )
    )
)

[jmsalomr]

Hi daXtreme,

Your reflections made me find the problem. Thank you very much!

At the end, the DAX was ok. The problem was in the relationships of the model. In one of them there was a both direction relationship between the Calendar Date and another fact table that was related, at the same time, with a dimmension table that was related with the original fact table. Changing the cross filter direction to Single did the trick.

Thanks again! 

[daXtreme]

You see? There's never anything supernatural going on. Always follow logic and reason and you'll get there (whatever the 'there' is). Glad you've found the problem and fixed it. Please, also take into account the measure I posted. It's always much better to have all the (helper) columns in the base tables (hide them if they are not needed by the end user) and then filter them using DAX simple conditions instead of doing calculations inside DAX. Not only will the model be more understandable but also your will your DAX lightning fast (even on ginormous models). I know that since I've been there.