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.

     

MattAllington's avatar
MattAllington
Community Champion
5 years ago

You can only sort a single column using a single sort column. The easiest "solution" is to load 2 sets of columns in the same table if needed. 

the ALLEXCEPT is removing filters. It is hard to say why it works vs other options without seeing the workbook (I am on iPad).