Forum Discussion

Raif8522's avatar
Raif8522
Frequent Visitor
3 years ago

DAX Formula - Circular dependency errors

Hi,

i have below Table on Power BI which i use it to publish customers' first visit after every 6 months. 

Visit Column: If customer doesn't have any previous visits, show date on the column where visit date matches

 

if(COUNTROWS(filter('Table','Table'[Customer]=EARLIER('Table'[Customer])=0),'Table'[Date],blank()))

 

Visit Suppression End Date Column:  Customers have 180 days suppression period, which visits in that period don't count as visit.

 

if('Table'[Visit]+180,if(DATEDIFF('Table'[Date],MAxx(FILTER('Table','Table'[Customer]=EARLIER('Table'[Customer]) && 'Table'[Trigger?]="Trigger"),'Table'[Date]+180),DAY)<=0,'Table'[Date],Blank()))

 

Trigger? Column: To find if it is a trigger 

 

if('Table'[Visit]<>Blank(),"Trigger",Blank())

 

 

Below is the Current Table:

After solving the problem, expected Table to be as below:

The problem is whatever formula or logic i try to find if customer visit on the Date is the visit happened after 6 months from the last triggered visit by suppressing the visits in between, is unsuccesful.

It is easy to find the initial visits and assign the Trigger on the "Trigger?" column but finding following visits, suppressing them and assign it as trigger based on 6 months rule is the problem. 

i am having circular dependency errors whatever i try by using those 3 calculated columns.

 

Any comment or help on the DAX formula from the ecperts will be very helpful for me to overcome this problem.

 

Many Thansk in advance.

 

 

2 Replies