Circular Dependency

[bh98381]

I have a DAX pattern that I've used in several projects for data grouping but I'm running into an issue with it now.  

I have a look up table that I use to retrieve a data group in this case SPRange and also a Sort Order for that group.

Here's the look up table:

Here's the DAX to retrieve the SPRange:

SP Range = 
CALCULATE(SELECTEDVALUE('SP_Range'[SPRange]),
     FILTER('SP_Range',
          [$ Sales] >= 'SP_Range'[Min]
          && [$ Sales] < 'SP_Range'[Max]))

This code performs as expected.

Here's the DAX to retrieve the SortOrder (Almost the same)

SPRangeSort = 
CALCULATE(SELECTEDVALUE('SP_Range'[SortOrder]),
     FILTER('SP_Range',
          [$ Sales] >= 'SP_Range'[Min]
          && [$ Sales] < 'SP_Range'[Max]))

This code also performs as expected when the SP Range function is not present.

The problem is when I try to add both functions I get the Circular Dependency issue. 

I think I'm missing something.  I have another file that is using this same pattern and still working.

Thanks for any help.

[v-frfei-msft]

Hi @bh98381 ,

Hope this case can help you. I recomend your to write measures instead of calculated columns here. If it doesn't solve your issue. Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[bh98381]

Thanks  @v-frfei-msft for your recomendation.

When I enter SP Range as a Calculated Columns I get the desired result and when I enter SPRangeSort as a measure there is not an error.  The issue with this is that I'm not able to use SPRangeSort as a Sort by Column for SP Range when it is a meaure.

When I enter SP Range as a Measure instead of a Calculated Column I am not able to use SP Range as a Slicer.

I'll need both of theses to be Calculated Columns.  I have this pattern workng in other PBIX files.