Forum Discussion

MaxSchrijen23's avatar
MaxSchrijen23
Helper 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 e...
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.

     

MaxSchrijen23's avatar
MaxSchrijen23
Helper I
4 years ago

Don't mind asking additional questions, I really appreciate your help so please ask if anything is unclear! The Id for the projects and Subprojects is also in the SubSubProjectId, I came to this solution via this article by Kasper. https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/. Where he also creates a column and calls it the SellerId which is the Id of the lowest level in the hierarchy you can be in, and then he adds rows for the total of each level and gives each level an Id. So, I also added a column called the SubSubProjectId and added the additional rows just for my Project and SubProject totals. 

MFelix's avatar
MFelix
Super User
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.

 

  • MaxSchrijen23's avatar
    MaxSchrijen23
    Helper I
    4 years ago

    Thanks a lot Felix!! This is exactly what I needed!