From Power Bi to Power Pivot

[marsclone]

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 )

[Fowmy]

@marsclone 

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

[edhans]

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]

@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]

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)
)