Power BI / DAX: Sort Calculated Table by Calculated Column

0
votes

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

Calculated_Dim_Table = VALUES('Source'[Dimension_Values])

Dimension_Values
a_order_third
b_order_first
c_order_second

Calculated_Order_Column = SWITCH('Calculated_Dim_Table'[Dimension_Values],"b_order_first",1,"c_order_second",2,"a_order_third",3)

*Sort 'Calculated_Dim_Table'[Dimension_Values] by 'Calculated_Dim_Table[Calculated_Order_Column] so that desired output is

Dimension_Values Calculated_Order_Column
b_order_first 1
c_order_second 2
a_order_third 3

Issue: Power BI is saying this is creating a circular dependency - is there another way to define the sort order of a column without referencing it to avoid the circular dependency?

1
sqlpowerbidax

1 Answers

0
votes

I think it throws an circular reference error, caused DAX internally decides the the order of execution. In this case, DAX wants to sort the dimension values upon loading, but it can't cause it depends on a calculated column thst in turn depends on dimension values. So it is a circular reference indeed. I would advise you to this in PQ and then load both the tables and then you can do the sorting in t2 with no trouble.