Building a Custom KPI filter

[Boja]

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?

[prateekraina]

Hi @Boja,

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

Prateek Raina

[Boja · Accepted solution]

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 🙂