Forum Discussion
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 |
| 1 | Title A | 50% |
| 2 | Title B | none |
| 3 | Title C | all new content |
| 4 | Title D | 50% |
| 5 | Title E | 25% |
| 6 | Title F | all new content |
| 7 | Title G | none |
| 8 | Title H | 50% |
| 9 | Title I | 25% |
| 10 | Title J | none |
I want to summarize the number of documents by PctNew. To do this, I've created a measure:
CountPctNew =
COUNT(Documents[PctNew])
(I'm using COUNT rather than COUNTX because I want a count of blank rows.)
I've also created a dimension table:
dim_PctNew =
DISTINCT(Documents[PctNew])
I join the tables with dim_PctNew[PctNew] on the 1 side and Documents[PctNew] on the many side.
Finally, I create a new column in the dim_PctNew table:
SortOrder =
SWITCH([PctNew],
"all new content", 1,
"75%", 2,
"50%", 3,
"25%", 4,
"none", 5,
6
)
Using a matrix visual, I can sum the occurrences of each PctNew value using the CountPctNew measure. But if I select dim_PctNew[PctNew], choose 'Sort by column' , and choose the SortOrder column, I get:
Error
A circular dependency was detected: dim_PctNew[PctNew],
dim_PctNew[SortOrder], dim_PctNew[PctNew]
(The same thing happens if I bypass the dimension table and simply add the SortOrder column to the Documents table.)
How can I create a sort order column without a circular dependency?
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.
2 Replies
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...
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.