Power Pivot - AverageX and Userelationship

[potap]

Hi!

I am trying to get the average time for each work order but using an inactive relationship on my Calendar table based on the opening date.

My measures :

Total=SUM(Workorder[Hours])

Total3 - OpenDate=CALCULATE (
    IF ( [Total] = 0, BLANK (), [Total] ),
    USERELATIONSHIP ( Workorder[OpenDate], Calendar[Date] )
)

=Average=
AVERAGEX (
    FILTER (
        VALUES ( Workorder[WorkOrderId] ),
        NOT ( ISBLANK ( [Total3 - OpenDate] ) )
    ),
    [Total3 - OpenDate]
)

The opening date of the work order is not always in the same year as the transactions. In this case, when I filter my pivot table by year, it doesn't always work.

In the file I attached, the average for a work order opened in 2016 with transactions only in 2017 won't work.

Link to xlsx file
https://drive.google.com/file/d/0BxcndF-RTt_oWTRiR3ZVZG93dlE/view?usp=sharing

Thank you very much!

 

[Matt Allington]

As I understand, the issue is you want to add up (or average) all the hours for a work order that commences on a date filtered by your slicer/filter even if the data is in a future year. If this is the case, then you can't use a relationship. It doesn't matter if the relationship is inactive or not, when activated the relationship first applies the filter (in your example 2017) and this will filter away any open date in a different year

I suggest you use an active relationship for your open order date (assuming that is what you want to filter on) and a virtual filter on the transactions (or the opposite). Assuming you do this, you can filter on 2016 to filter all work orders opened that year, then write a virtual filter something like this

calculate([total], all(calendar), filter(workorder,workorder[transaction date]>=min(calendar[date])))

i think this will work. I haven't tested it though

 

[potap]

Hi Matt!

What I don't understand is why DAX thinks the data is in a future year if I ask to use the relationship with OpenDate? Everything is in 2016 when the relationship used is based on OpenDate. It does work for the sum formula (Total3 - Open Date) but not with the average!

This table is used for a lot of indicators so I need my active relationship to be with TransactionDate. I could load a second table and set my relationship with OpenDate on this one but it is a huge table and I would prefer to avoid that.

 

[Matt Allington]

Your formulas are very complex. Each measure has an implicit calculate wrapped around it. When you embed the measures inside other measures you can lose sight of what you are building. I have expanded your formula for you here.

=AVERAGEX (
    FILTER (
        VALUES ( Workorder[WorkOrderId] ),
        NOT (
            ISBLANK (
                CALCULATE (
                    IF (
                        CALCULATE ( SUM ( Workorder[Hours] ) ) = 0,
                        BLANK (),
                        CALCULATE ( SUM ( Workorder[Hours] ) )
                    ),
                    USERELATIONSHIP ( Workorder[OpenDate], Calendar[Date] )
                )
            )
        )
    ),
    CALCULATE (
        IF (
            CALCULATE ( SUM ( Workorder[Hours] ) ) = 0,
            BLANK (),
            CALCULATE ( SUM ( Workorder[Hours] ) )
        ),
        USERELATIONSHIP ( Workorder[OpenDate], Calendar[Date] )
    )
)

I can't tell you why it isn't working, but I can tell you that each calculate caused context transition and you have 2 nested functions that contain a row context (AVERAGEX and FILTER).

I just took a step back and this is what I wrote. Does this give you what you need?

Average =CALCULATE (
    DIVIDE ( [Total2 - Without 0], COUNTROWS ( Workorder ) ),
    USERELATIONSHIP ( Workorder[OpenDate], Calendar[Date] )
)

Interim measures are great when you are trying to break your problem into pieces. But in this case you have created layers of complexity without realising it.

 

[Matt Allington]

DAX has 2 main processes - filtering and evaluating. When you use USERELATIONSHIP you are telling DAX that you want the calendar to filter based on OpenDate. When you put a filter on OpenDate = 2016, there is only 1 row that is returned. Is that what you expect?