Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
25 | |
23 | |
15 | |
11 |
User | Count |
---|---|
77 | |
61 | |
47 | |
18 | |
12 |