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 with the sort order.

 

I am able to pull in the ordering for the inactive relationship using this approach:

FW Order =
CALCULATE(MINX('Table',RELATED('Order, Status'[Order])),
  USERELATIONSHIP('Table'[FW],'Order, Status'[Status]),
  ALLEXCEPT('Table','Table'[FW]))

 

which I gathered from the thread below.
https://community.powerbi.com/t5/Desktop/using-inactive-relationship/m-p/1195011

 

I don't understand why I need the ALLEXCEPT clause. (Though the column doesn't have correct data without it.) And when I try to sort the FW column by this new FW Order field, I get the error "A circular dependency has been detected".

 

I can solve this problem by creating a second Status Order table and just have two active links, one to each table. But I am trying to avoid that complexity, and get a better understanding of DAX!

 

Here is my PBIX file:

https://austincollege-my.sharepoint.com/:u:/g/personal/aseagull_austincollege_edu/EbAhlI39pmxKoPPtDKzmsQ4BrL_9k_rcb43tb_DT8FeSTw?e=wA2oTe

 

Any ideas why I'm getting the error? Any explanation of the need for ALLEXCEPT? Thank you!

Amon

 

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.

     

6 Replies

  • 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). 

    • aseagull's avatar
      aseagull
      Helper IV
      5 years ago

      Matt, thanks for the quick reply! I want to be able to sort both the FW and the AW columns on the same sort order. One of them (AW) is linking to the sorting table via an active relationship, so I'm able to pull in the linked field in my viz. I also want to sort the FW field on that same sort order. It might make more sense once you're able to look at the linked file.

       

      Regarding ALLEXCEPT, I guess I don't understand why I need anything like that. I thought all you needed to do, to use an inactive relationship, was evaluate an expression in the context of that relationship. ?

    • 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.