dax - circular reference error with two measures using same filter

[Anonymous]

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. 

[v-yiruan-msft]

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

[Anonymous]

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]

 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]

@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)

[amitchandak]

@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)