Forum Discussion

Yaa_D's avatar
Yaa_D
Frequent Visitor
4 years ago

'Working on it' hangs when combining multiple calculation into one column

Hi All,  I have a specific calculation that works OK when seperated to different columns, but hangs when trying to combine it all into one column. The specifics:  I have a table that gets update...
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

Rather than using MAX and LOOKUPVALUE, just use TOPN

Total diff =
var currentTotal = 'table1'[total]
var currentDate = 'table1'[fileDate]
var prevTotal = SELECTCOLUMNS( TOPN(1, FILTER( ALLEXCEPT( 'table1', 'table1'[id]), 
   'table1'[fileDate] <= currentDate - 28 ), 'table1[fileDate], DESC), "@val", 'table1[total])
return currentTotal - prevTotal
Yaa_D's avatar
Yaa_D
Frequent Visitor
4 years ago

Thank you for the reply! I really have a lot to learn in Power BI, most of the time I'm just using the tools I already know that do the job.
When trying the solution you offered I get the error "A circular dependency was detected" on Total diff column, do you have any thoughts on why this is happening?
Thanks again!

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    its possible that the column itself is being included in the TOPN calculations, try replacing that line with

    var prevTotal = SELECTCOLUMNS( TOPN(1, SELECTCOLUMNS(
   FILTER( ALLEXCEPT( 'table1', 'table1'[id]), 
   'table1'[fileDate] <= currentDate - 28 ), 
   "@fileDate", 'table1[fileDate], "@value", 'table1[total])
@fileDate, DESC), "@val", [@value])