Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

dax - circular reference error with two measures using same filter

Hi All,

 

I am trying to create slicers based of sales quantity (no sales) over time. I can create the first measure which works well however if I create a second one with a different date filter I get a circular reference error. Can anyone please let me know how best to resolve this?

 

This works.....

 

No Sales 3 Months =
CALCULATE(
'tablename'[Quantity] == 0, Date[0-12 MonthsFlag] = TRUE()
)
 
If I create another I get the circular reference error:

No Sales 6  to 12 Months =
CALCULATE(
'tablename'[Quantity] == 0, Date[6-12 MonthsFlag] = TRUE()
)
 
Any assistance greatly appreciated. 
Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , refer to this blog, how can you get no sales by just using ins blank

    https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

     

    example

     

    Rolling 3 =

    var _1=  CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),0),-3,MONTH))

     

    return

    if(isblank(_1), 1, 0)

     


    Rolling 6 before 6 =

    var _1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),-6),-6,MONTH))

     

    return

    if(isblank(_1), 1, 0)

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     Amit,

     

    The first issue I see is that I cannot use Blanks as I have many records that are actually blank and I need to exclude these. I need this to work only when the value is == 0. The second is that I wish to use this measure as a slicer.. as in True or False. I do like your example. Thank you.

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      Anonymous , you can try like

      Rolling 3 =

      var _1= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),0),-3,MONTH))+0

       

      return

      if(_1 =0 , 1, 0)

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks Amit,  I cannot make blanks = 0, I need strictly values that  = 0.
    I have thousands of records that are blank and thousands that = 0. I only want values that are strictly 0 in value and exlude all that are blank.
    I do understand your code in sort but this is not working for me. Kind Regards Fred

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    You can refer the method in the following links to resolve the problem of circular reference error. If they can't help you resolve the problem, please share with us about the sample data in fact able and the related formula of column [0-12 MonthsFlag] &[6-12 MonthsFlag] in order to provide you a suitable solution.

    Circular reference error

    Avoiding circular dependency errors in DAX

    Best Regards