## Desktop

Highlighted
Member
Posts: 47
Registered: ‎04-27-2017

# Building a Custom KPI filter

[ Edited ]

Hello,

I got stuck, so I would greatly appreciate your wisdom.

I am trying to create a metric TREND as a calculated column so i can create a filter later from it. It works well as a measure, but no filter can be created from it. To calculate TREND i am using two calculated columns VisitCount Yesterday and VisitCount All Previous Days (sum of all visits from the start date)

TREND = VisitCount Yesterday / VisitCount All previous days

```VisitCount Yesterday =
CALCULATE ( SUMX('Table(2)'[Visits]), 'Table(2)'[VisitDate] >= TODAY()-1 )

VisitCount All previous days =
CALCULATE( SUMX('Table(2)','Table(2)'[Visits]) , ALLEXCEPT('Table(2)', 'Table(2)'[FaId]))Trend ='Table(2)'[VisitCount Yesterday]/'Table(2)'[VisitCount All Previous Days]```

Calculated columns are ok, but when divided, I got an error of Circular dependency:

Do you have any idea how to solve it?

Accepted Solutions
Member
Posts: 47
Registered: ‎04-27-2017

## Re: Building a Custom KPI filter

Thanks, Prateek!

I realise that this problem is solved by creating calculated table (including only yesterday values) to avoid having emtpy rows. Apparently they were the sourcse of circular dependency. It works well now. Thanks

All Replies
New Contributor
Posts: 504
Registered: ‎02-16-2017

## Re: Building a Custom KPI filter

Hi @Boja,

Please go thorugh this article which explains why Circular Dependency occurs and how you should avoid it.

Prateek Raina

Member
Posts: 47
Registered: ‎04-27-2017

## Re: Building a Custom KPI filter

Thanks, Prateek!

I realise that this problem is solved by creating calculated table (including only yesterday values) to avoid having emtpy rows. Apparently they were the sourcse of circular dependency. It works well now. Thanks