Forum Discussion

marsclone's avatar
marsclone
Helper IV
4 years ago
Solved

Transform PBI Formula to DAX Power Pivot

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

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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's avatar
    wdx223_Daniel
    4 years ago

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

4 Replies

  • az38's avatar
    az38
    Community Champion
    4 years ago

    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's avatar
      marsclone
      Helper IV
      4 years ago

      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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

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