Forum Discussion

aseagull's avatar
aseagull
Helper IV
5 years ago
Solved

Using inactive relationship cannot sort due to circular dependency

Hello and Happy New Year. I have two columns (FW and AW) with the same type of data (Status), which has its own sort order.   So I have 1 active and 1 inactive relationship to that dimension table ...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi aseagull ,

     

    Firstly, you could create a calculated table.

    Table 2 = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[ID], 'Table'[AW], 'Table'[FW]
    ),
    "AW_Order",
    LOOKUPVALUE(
        'Order, Status'[Order],
        'Order, Status'[Status], 'Table'[AW]
    ),
    "FW_Order",
    LOOKUPVALUE(
        'Order, Status'[Order],
        'Order, Status'[Status], 'Table'[FW]
    )
)

    Then, you can use the "Sort by column" feature.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-lionel-msft's avatar
v-lionel-msft
Community Support
5 years ago

Hi aseagull ,

 

How about doing it like this?

 

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

aseagull's avatar
aseagull
Helper IV
5 years ago

Lionel, thanks for this suggestion. I haven't seen LOOKUPVALUE.

 

I'm still hitting the same error when I try to set this new column as the "sort by" for FW. Screenshot below. Were you able to set it as the sort column?

 

Thanks,

Amon

 

 

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

    Hi aseagull ,

     

    Firstly, you could create a calculated table.

    Table 2 = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[ID], 'Table'[AW], 'Table'[FW]
    ),
    "AW_Order",
    LOOKUPVALUE(
        'Order, Status'[Order],
        'Order, Status'[Status], 'Table'[AW]
    ),
    "FW_Order",
    LOOKUPVALUE(
        'Order, Status'[Order],
        'Order, Status'[Status], 'Table'[FW]
    )
)

    Then, you can use the "Sort by column" feature.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • aseagull's avatar
      aseagull
      Helper IV
      5 years ago

      Lionel, thank you! I appreciate this very much. So creating the calculated table effectively "hides" the circularity, I guess? I see this works even skipping the SUMMARIZE step...weird. Thank you again!