cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kerrymr Frequent Visitor
Frequent Visitor

Impact of Measures, visuals, and relationships

Hi Experts,

BackGround...

 

 BiLayout.JPGSlicers.JPG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kerrymr Frequent Visitor
Frequent Visitor

Re: Impact of Measures, visuals, and relationships

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.

 

 

 

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Impact of Measures, visuals, and relationships

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Kerrymr Frequent Visitor
Frequent Visitor

Re: Impact of Measures, visuals, and relationships

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.

 

 

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 336 members 3,275 guests
Please welcome our newest community members: