topic Re: From Power Bi to Power Pivot in DAX Commands and Tips

From Power Bi to Power Pivot

marsclone — Mon, 02 Aug 2021 13:36:31 GMT

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 )

Re: From Power Bi to Power Pivot

edhans — Mon, 02 Aug 2021 14:18:26 GMT

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.

Re: From Power Bi to Power Pivot

Fowmy — Mon, 02 Aug 2021 14:22:43 GMT

marsclone

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

Re: From Power Bi to Power Pivot

marsclone — Mon, 02 Aug 2021 14:49:04 GMT

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

Re: From Power Bi to Power Pivot

edhans — Mon, 02 Aug 2021 15:14:25 GMT

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