Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Parent-Child Hierarchy Filtering Conditions

Hi, I am very new in Power BI and have following issue to find the right formula combination in Power BI. Is this possible in Power BI and if yes how can I realize thi? Thanks in Advance 🙂   ...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Allison,

 

I transferred your suggestion to the real table as below but it says as error "Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2." What does this mean? 

 

TEST = IF(ISBLANK(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"A";IF(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]= "Apple" && [State]="Done");[Parent Work Item Id])=MAXX(FILTER('Project Tracker';[Title]= "Pear"&& [State]="To Do";[Parent Work Item Id]); "YES"; "NO")))

 

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous  That means you have a syntax error, you need to close the FILTER function with ) 

 

Try adding the red ) as below and delete the final 😞 

 

TEST = IF(ISBLANK(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"A";IF(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]= "Apple" && [State]="Done");[Parent Work Item Id])=MAXX(FILTER('Project Tracker';[Title]= "Pear"&& [State]="To Do");[Parent Work Item Id]); "YES"; "NO"))

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    AllisonKennedy The formula works now, thanks!

    But as result it shows in the new "TEST" column in all cells the red "A" from the formula below - do you think we missed here something else?

     

    TEST = IF(ISBLANK(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"A";IF(MAXX(FILTER('Project Tracker';'Project Tracker'[Title]= "Apple" && [State]="Done");[Parent Work Item Id])=MAXX(FILTER('Project Tracker';[Title]= "Pear"&& [State]="To Do");[Parent Work Item Id]); "YES"; "NO"))

    • AllisonKennedy's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Sorry, you can change the red A to "No" and are you doing this as a MEASURE or Column? It will not work as a Column, so needs to be done as a MEASURE.

       

      Do as a MEASURE, but you will need to use it in the right configuration, so only works/makes sense when used in a Matrix or Table with Parent Name or ID in the rows. 

       

      Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

       

      If you found this post helpful, please give Kudos.

      I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

      https://sites.google.com/site/allisonkennedycv

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi AllisonKennedy ,

        I changed "A" to "NO", put the formula in a measure and involved in a matrix report - but there is no change. All the values are shown with "NO" now.

         

        Would it be possible for you to explain the 3 areas of the formula - maybe I can check the steps from myside when I am able to understand the formula?

         

        TEST =
        IF (ISBLANK (MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id]));"NO";
         
        IF (MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Apple" && 'Project Tracker'[State]="Done");'Project Tracker'[Parent Work Item Id])=
         
        MAXX(FILTER('Project Tracker';'Project Tracker'[Title]="Pear" && 'Project Tracker'[State]="To Do");'Project Tracker'[Parent Work Item Id]);"YES";"NO")
        )