Forum Discussion

marsclone's avatar
marsclone
Icon for Helper IV rankHelper IV
5 years ago

From Power Bi to Power Pivot

Hello,

 

I want to use this formula in Power Pivot 2016. Is that possible?

Thank you in advance! Kind regards Marcel

 

ScopeCurrentLevel =


var level4 = if(ISINSCOPE(Financial_Statement_Layouts[Level 4]),4)
var level3 = if(ISINSCOPE(Financial_Statement_Layouts[Level 3]),3)
var level2 = if(ISINSCOPE(Financial_Statement_Layouts[Level 2]),2)
var level1 = if(ISINSCOPE(Financial_Statement_Layouts[Level 1]),1)

return COALESCE(level4,level3,level2, level1 )
Need Help

4 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    No. ISINSCOPE is relatively new, and Power Pivot hasn't had a new function since TREATAS was added in 2016 I think.

    You'll have to use the older method of a combination of HASONEVALUE(), HASONEFILTER(), and/or ISFILTERED()

    They will accomplish the same thing, but as you can see in Power BI, ISINSCOPE is much easier to use.

    • marsclone's avatar
      marsclone
      Icon for Helper IV rankHelper IV
      5 years ago

      edhans Fowmy 

       

      You are both right!

      My question is, how can i adjust this formula so it will work in Power Pivot. 

      Thank you in advance 

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        5 years ago

        It depends marsclone - it isn't a simple swap. We'd have to see your report, your filter context, and the expected result.

        COALESCE is easy though - 

        IF(
   ISBLANK(Something),
   0,
   ISBLANK(Something)
)
  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    marsclone 

    Agree with edhans , ISINSCOPE and COALESCE are not available in Power Pivot, you will have to use other functions to accomplish the same