Impact of Measures, visuals, and relationships

[Kerrymr]

Hi Experts,

BackGround...

 

I have multiple other visuals on the slicer page that interact with the timeline slicer, it cannot be deleted.

The open hours visual is supposed to sum up all the executable hours for each department number (19, 6, etc.).

The timeline visual is based on the DateDim table, field Date.

There is an inactive relationship between DateDim(date) and TreleasedRotuer(ReceiptDate)

Receiptdate is null if the parts have not been received in the area.

The equation for executable hours is in a calculated column with Dax as follows...

TotalExecutableMachineHours = 
IF (
    [TotalCompleteQty] = 0,
    CALCULATE (
        SUMX (
            TReleasedRouter,
            TReleasedRouter[SetupMachineStd]
                + TReleasedRouter[OpQty] * TReleasedRouter[MachineTimePerPiece]
        ),
        ISBLANK ( TReleasedRouter[OpCompleteDate] ),
        NOT ISBLANK ( TReleasedRouter[ReceiptDate] ),
        USERELATIONSHIP(TReleasedRouter[ReceiptDate],DateDim[Date])
    ),
    CALCULATE (
        SUMX (
            TReleasedRouter,
            ( TReleasedRouter[OpQty] - [TotalCompleteQty] )
                * TReleasedRouter[MachineTimePerPiece]
        ),
        ISBLANK ( TReleasedRouter[OpCompleteDate] ),
        NOT ISBLANK ( TReleasedRouter[ReceiptDate] ),
        USERELATIONSHIP(TReleasedRouter[ReceiptDate],DateDim[Date])
    )
)

 The results are not showing up correct.

I can get the correct results if i delete the timeline visual (which is not acceptable).

I think this means that the totalexecutable results are being filtered thorugh the variance table path?  

I have tried:

    Using a Filter, Filter(all) and some other functions in the filter section of the calculate.  Didn't work.

    Changing the date relationship between receiptdate and Date to both directions.  didnt work.

    Various other approaches.

Any help you guys can provide would be great.

K.

[MFelix]

Hi @Kerrymr,

Do you want the visual to be affected by the timeline selection? If no then kust change the interactions between the timeline and the visual to no effect and the result of that is the same as deleting the timeline.

Not on computer to make further analysis on this, will get back to you later.

Regards,
MFelix

[Kerrymr · Accepted solution]

The problem was resolved there appears to have been two underlying issues.

1.  I should have used a measure instead of a calculated column. 

• For reference: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
  • "When one defines a calculated column, they are writing a DAX expression that will be executed in a row context. Since USERELATIONSHIP requires a CALCULATE to be used and the CALCULATE applies a context transition when executed within a row context, obtaining the desired behavior is not easy."

Changing to a measure resolved the calculation issue.

2. Per M.Felix's reccomendation, setting the no interaction for the visual likely prevented a possible issue where the user changed the visual and changed the equation results.

Thanks

K.