Circular dependency error with sort order column

[Anonymous]

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?

[Anonymous]

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 · Accepted solution]

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.