Forum Discussion

Dudeman's avatar
Dudeman
Helper I
5 years ago
Solved

fixing circular reference in calculated column

Hello. 

 

I'm getting a circular reference I can't figure out how to fix when creating a calculated column version of a calculated measure.

Here is my workbook Airport code workbook (wetransfer link).

 

I've had some community help (thanks!) to get my calculated measure Result Destination working, but becuase I want to link two tables, I need it to be a calculated column instead. 

 

In the workbook I have the table setup exactly with the correct output. The circular reference is in a calculated column called Result Destination Col. I want to fix that and then replace the Result Desination measure with this column in my visualization.

 

Thanks so much.

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi Dudeman   ；

     

    When you use Live connection, you can not add columns,  So maybe you could try to modify the MaxArrival2 column by the following formula:

    MaxArrival2 =
CALCULATE (
    MAX ( 'Legs'[arrivalTime] ),
    FILTER (
        ALL ( 'Legs' ),
        [BookingNumber] = EARLIER ( [BookingNumber] )
            && [ArrivalDate] = EARLIER ( [ArrivalDate] )
    )
)

    The final output is shown below:  

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

5 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi Dudeman   ；

     

    When you use Live connection, you can not add columns,  So maybe you could try to modify the MaxArrival2 column by the following formula:

    MaxArrival2 =
CALCULATE (
    MAX ( 'Legs'[arrivalTime] ),
    FILTER (
        ALL ( 'Legs' ),
        [BookingNumber] = EARLIER ( [BookingNumber] )
            && [ArrivalDate] = EARLIER ( [ArrivalDate] )
    )
)

    The final output is shown below:  

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    The first thing to do is to rewrite the column DAX so it's not using a measure i.e. [MaxArrival].  A calculated column should not use a measure (it's possible to do it but definitely not advisable)

     

    Edit: I've just seen that [ArriveConnect] is a measure too so same advice applies.

    • Dudeman's avatar
      Dudeman
      Helper I
      5 years ago

      Thanks HotChilli. Do you know how I could re-write the DAX? I'm very new to all of this.

      I'm not too sure how to re-write it becuase I need to be able to still reference something like MaxArrival and ArriveConnect to get my end result, don't I?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi Dudeman  , 

     

    You could create two columns by the following formula: 

    MaxArrival2 = CALCULATE(MAX( 'Legs'[arrivalTime]),ALLEXCEPT ('Legs','Legs'[BookingNumber],'Legs'[ArrivalDate]))
    Result Destination 2 =
IF (
    [arrivalTime]
        = CALCULATE (
            MIN ( [arrivalTime] ),
            FILTER (
                ALLEXCEPT ( Legs, Legs[BookingNumber] ),
                [arrivalTime] = [MaxArrival2]
            )
        ),
    [arrivalAirport]
)

    The final output is shown below:  

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

    • Dudeman's avatar
      Dudeman
      Helper I
      5 years ago

      v-yalanwu-msft thanks Yalan. That does work in the sample file and I appreciate the help so much.

      However, when I try it in my actual file I'm unable to get the Calculate or AllExcept functions to work in the formula. I think it has to do with the fact I'm connected to a live PBI dataset my BI team has created (or the fact I'm on an Enterprise version of PBI maybe). 

      Is there a workaround for live datasets? I tried to add the column to the Master Airports table, where it does work, but when I add it to the viz it breaks and a black box pops up and disappears very quickly with some kind of SQL error. I can't screenshot it - it's too fast.

       

      Thoughts?