cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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
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 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
Helper IV

You are both right!

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors