I have a Power BI data model with a DAX calculated table used as a dimension that is calculated with VALUES from a fact table with multiple occurrences of each value, so it is a 1:M relationship.
In my report I want to use the calculated table values as a slicer with the options presented in a specific order other than alphabetical. Therefore in the calculated table, I created a calculated column that assigns a number to each row based on a SWITCH function. However, when I then try to "Sort by Column" in Power BI, it throws an error indicating that it is a circular reference. How else can I define the sort order?
Here is an example data/calculated tables:
'Source' fact table:
| Dimension_Values |
| a_order_third |
| a_order_third |
| b_order_first |
| b_order_first |
| c_order_second |
| c_order_second |