Transform PBI Formula to DAX Power Pivot

[marsclone]

Hi,

The file below uses a formula that I would also like to use in Power Pivot DAX.

File 

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 )

I have already tried "Isfiltered", "Hasonevalue", "Hasonefilter", but all give me a semantic error, that for column "Level 2" no single value can be determined.

ScopeCurrentLevel:=

var level4 = IF(HASONEFILTER(Financial_Statement_Layouts[Level 4]);4)
var level3 = IF(HASONEFILTER(Financial_Statement_Layouts[Level 3]);3)
var level2 = IF(HASONEFILTER(Financial_Statement_Layouts[Level 2]);2)
var level1 = IF(HASONEFILTER(Financial_Statement_Layouts[Level 1]);1)

return  IF(Financial_Statement_Layouts[Level 4]<>"";Level4;IF(Financial_Statement_Layouts[Level 3]<>"";Level3;IF(Financial_Statement_Layouts[Level 2]<>"";Level2;Level1)))

Is it possible to convert this formula in such a way, that I can also apply it in Power Pivot?

Thanks and regards,

Marcel

[v-angzheng-msft · Accepted solution]

Hi, @marsclone 

As far as I know, since Power BI is based on Power Pivot, a formula can be used in Power BI, then it can be used in Power Pivot. 

It should be noted that the lower level also belongs to the higher level. So if the judgment is not nested, then there may be multiple values.

You may need to modify your formula like below:

ScopeCurrentLevel =
VAR level =
    IF(
        ISINSCOPE( Financial_Statement_Layouts[Level 4] ),
        IF(
            ISINSCOPE( Financial_Statement_Layouts[Level 3] ),
            IF(
                ISINSCOPE( Financial_Statement_Layouts[Level 2] ),
                IF( ISINSCOPE( Financial_Statement_Layouts[Level 1] ), 0, 1 ),
                2
            ),
            3
        ),
        4
    )
RETURN
    level

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel · Accepted solution]

=if(isfiltered(Financial_Statement_Layouts[Level 1]),if(isfiltered(Financial_Statement_Layouts[Level 2]),if(isfiltered(Financial_Statement_Layouts[Level 3]),if(isfiltered(Financial_Statement_Layouts[Level 4]),4,3),2),1))

[v-angzheng-msft · Accepted solution]

Hi, @marsclone 

As far as I know, since Power BI is based on Power Pivot, a formula can be used in Power BI, then it can be used in Power Pivot. 

It should be noted that the lower level also belongs to the higher level. So if the judgment is not nested, then there may be multiple values.

You may need to modify your formula like below:

ScopeCurrentLevel =
VAR level =
    IF(
        ISINSCOPE( Financial_Statement_Layouts[Level 4] ),
        IF(
            ISINSCOPE( Financial_Statement_Layouts[Level 3] ),
            IF(
                ISINSCOPE( Financial_Statement_Layouts[Level 2] ),
                IF( ISINSCOPE( Financial_Statement_Layouts[Level 1] ), 0, 1 ),
                2
            ),
            3
        ),
        4
    )
RETURN
    level

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @marsclone 

Use Power query for retrieve, cleansing and transform data

HASONFILTER() and ISINSCOPE() is a filter functions that works over the whole loaded data model in full context and depends on, for example, user behaviour

[marsclone]

Hi @az38 

Thank you for your reply. I adjusted the post, i meant if this formula can be adjusted so this can be used in Power Pivot.