Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
CloudHerder
Resolver I
Resolver I

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:

DocIDTitlePctNew
1Title A50%
2Title Bnone
3Title Call new content
4Title D50%
5Title E25%
6Title Fall new content
7Title Gnone
8Title H50%
9Title I25%
10Title Jnone

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?

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

View solution in original post

2 REPLIES 2
CloudHerder
Resolver I
Resolver I

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors