Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
marsclone
Helper IV
Helper IV

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 )
4 REPLIES 4
Fowmy
Super User
Super User

@marsclone 

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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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 

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


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.