Forum Discussion

MaxSchrijen23's avatar
MaxSchrijen23
Icon for Helper I rankHelper I
4 years ago
Solved

Add String value to correct hierarchy levels with ISINSCOPE

Dear all,

I want to create a matrix containing a hierarchy level within this matrix. It is a matrix containing Projects, Subprojects and SubSubProjects, then I also want to add a Project leader on each of the levels of the hierarchy matrix, so also the top ones. In order to create this I made use of the following article about the ISINSCOPE function to find out on which level you are in the matrix. 

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Unfortunately, I can't get this to work and it seems that it only works for numeric values and not for string values. A screenshot of my data is below and a download link to files as well. 

 

https://filedropper.com/d/s/M2ltIzfZsVqEjRYctmQ9ZEucv4YVPv

Does somebody have a solution for this?

Thanks in advance!

 

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi MaxSchrijen23 ,

     

    I have done the following measure:

    Leader Value = 
LOOKUPVALUE (
    ProjectData[Leader],
    ProjectData[SubSubProjectId],
        SWITCH (
            TRUE (),
            ISINSCOPE ( ProjectHierarchy[SubSubProjects] ), SELECTEDVALUE ( ProjectHierarchy[SubSubProjectId] ),
            ISINSCOPE ( ProjectHierarchy[SubProject] ),
                CALCULATE (
                    SELECTEDVALUE ( ProjectHierarchy[SubSubProjectId] ),
                    ProjectHierarchy[IsTotal] = 1
                        && ProjectHierarchy[SubProject] = SELECTEDVALUE ( ProjectHierarchy[subProject] )
                ),
            ISINSCOPE ( ProjectHierarchy[Project] ),
                CALCULATE (
                    SELECTEDVALUE ( ProjectHierarchy[SubSubProjectId] ),
                    ProjectHierarchy[IsTotal] = 2
                        && ProjectHierarchy[Project] = SELECTEDVALUE ( ProjectHierarchy[Project] )
                )
        )
)

     

    Result is below:

    Now you can make some tweaks to hide the values that are blank but don't believe it's the best option.

     

    In this case you need to use a Parent Child hierarchy. Using this post I have made some changes to your model:

     

    Result is below:

    This passes to having your projects table in the following format:

     

     

    I have all the formulas and results in the table ProjectHierarchy (2). Please tell me what you think.

    See attach Excel file and PBIX file.

     

8 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi MaxSchrijen23 

     

    The ISINSCOPE works on any type of data, it only check if the line of the matrix is within the column you refer so if you have ISINSCOPE(Subsub-Project) all the lines of subsbuprojects are true and all other are false, however if you have ISINSCOPE(Project) all the lines will give true because the Project is the main level so a project, subproject and subsubproject have a project so all lines return true.

     

    I cannot download the file you shared can you please share the formulas that you are using for your measures?

     

    • MaxSchrijen23's avatar
      MaxSchrijen23
      Icon for Helper I rankHelper I
      4 years ago

      Hi Felis, thanks for your reaction. I indeed used the IsInScope to get the name of the level at which I am so, I think I've done this correctly. However, my main issue is now getting the correct attribute data also on the correct level. So, the the leader of the upper project on the upper level and the leader of thesub project on the subproject level etc. 

      The measure I used are as follows:

      Measure =
      IF ( ISINSCOPE ( ProjectHierarchy[SubSubProjects] ), TRUE () ). To test if I am on the SubSubProjectLevel.
      Measure 2 =
      SWITCH (
      TRUE,
      ISINSCOPE ( ProjectHierarchy[SubSubProjects] ), "SubSubProject",
      ISINSCOPE ( ProjectHierarchy[SubProject] ), "SubProject",
      ISINSCOPE ( ProjectHierarchy[Project] ), "Project",
      "Other"
      ). To return the level I am at.
      Measure 3 =
      VAR subsubprojectselected = ISINSCOPE(ProjectHierarchy[SubSubProjects])
      VAR subsubprojectselected = ISINSCOPE(ProjectHierarchy[SubProject])
      VAR subsubprojectselected = ISINSCOPE(ProjectHierarchy[Project]) and this one, however this one is not finished as I need to combine this with some kind of function that returns the correct project leader per project. For this I used the article by KasperdeJonge that I included in the first post.
       
       

      I made new download link to a google drive file, hope this works!

      https://drive.google.com/drive/folders/1780SvcwSNIJgTgEwD8_hgD2iB9sqO0z9?usp=sharing

       

      Hope these things help and that you can help me :).

      Thanks in advance!

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        4 years ago

        Hi MaxSchrijen23 ,

         

        Looking at your data I have a question, you only have a project leader on the SubSubproject so for project 1 who is the project leader, since the Project 1 has SubsubProject1 and subsubproject2 so PersonA and Person B respectively. Wich of the two is the project Leader and by defauilt the Subproject leader.