Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Circular dependency error with sort order column

I have a Documents fact table with several columns including a DocID and one that indicates the percent of the document that is new content. The structure looks like this: DocID Title PctNew ...
Circular dependency
sort order
  • Anonymous's avatar
    Anonymous
    3 years ago

    I think I've finally figured this out, with help from other sources. Here are the steps:

       1. As discussed in the comments of this thread, rather than creating a dimension table using VALUES() or DISTINCT(), create the table using SUMMARIZE() as follows:

     

     

    dim_PctNew = 
SUMMARIZE(Documents, Documents[PctNew],
    "Sort Order", SWITCH(Documents[PctNew],
        "all new content", 1,
        "75%", 2,
        "50%", 3,
        "25%", 4,
        "none", 5,
        6
    )
)​

     

     

       2. Create the 1:M relationship between dim_PctNew[PctNew] and Documents[PctNew].

       3. Add a column to the fact table using RELATED(dim_PctNew[Sort Order]).

       4. Create a measure

     

     

    CountPctNew = 
COUNT(Documents[DocID])​

     

     

       5. In the matrix visual, set the rows to Documents[PctNew] and the values to CountPctNew.

       6. In the Data pane, click on the dim_PctNew[PctNew] field, then in the top Column tools ribbon choose 'Sort by column' and select Sort Order.

       7. To get calculate the percent of column totals, create a measure:

     

     

    PctNew % of Total = 
DIVIDE([CountPctNew],
    CALCULATE([CountPctNew], ALLSELECTED(dim_PctNew[PctNew]), ALLSELECTED(dim_PctNew[Sort Order])),
    BLANK()
)​

     

     

    It was the second ALLSELECTED() in the last step that I had missed. Without it, all percentages are 100%. I was able to figure it out by using the built-in 'Show value as' > 'Percent of column total' for the matrix Values field and then by using Performance analyzer to refresh the visual and examine the code in DAX Studio.

     

    I still have lots to learn.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

FWIW, I've tried two possible solutions. One is the create a conditional column in Power Query that has the same logic as the SWITCH() statement above. The other is to create the dimension table using SUMMARIZE and add a "Sort Order" column defined with the SWITCH() statement.

 

The good news is they both allow me to sort by using the Sort Order column.

 

The bad news is that I have a measure to compute percent of column total:

Pct of Total =
DIVIDE([CountPctNew]
   CALCULATE([CountPctNew], ALLSELECTED(dimPctNew[PctNew])),
   BLANK()
)

This gives the correct values if I DON'T sort by Sort Order.

If I DO sort by Sort Order, all row values are 100%.

 

Curiouser and curiouser...

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I think I've finally figured this out, with help from other sources. Here are the steps:

       1. As discussed in the comments of this thread, rather than creating a dimension table using VALUES() or DISTINCT(), create the table using SUMMARIZE() as follows:

     

     

    dim_PctNew = 
SUMMARIZE(Documents, Documents[PctNew],
    "Sort Order", SWITCH(Documents[PctNew],
        "all new content", 1,
        "75%", 2,
        "50%", 3,
        "25%", 4,
        "none", 5,
        6
    )
)​

     

     

       2. Create the 1:M relationship between dim_PctNew[PctNew] and Documents[PctNew].

       3. Add a column to the fact table using RELATED(dim_PctNew[Sort Order]).

       4. Create a measure

     

     

    CountPctNew = 
COUNT(Documents[DocID])​

     

     

       5. In the matrix visual, set the rows to Documents[PctNew] and the values to CountPctNew.

       6. In the Data pane, click on the dim_PctNew[PctNew] field, then in the top Column tools ribbon choose 'Sort by column' and select Sort Order.

       7. To get calculate the percent of column totals, create a measure:

     

     

    PctNew % of Total = 
DIVIDE([CountPctNew],
    CALCULATE([CountPctNew], ALLSELECTED(dim_PctNew[PctNew]), ALLSELECTED(dim_PctNew[Sort Order])),
    BLANK()
)​

     

     

    It was the second ALLSELECTED() in the last step that I had missed. Without it, all percentages are 100%. I was able to figure it out by using the built-in 'Show value as' > 'Percent of column total' for the matrix Values field and then by using Performance analyzer to refresh the visual and examine the code in DAX Studio.

     

    I still have lots to learn.