Matrix visual with ISINSCOPE measure is close but not quite

[TasCat09]

I have a single table in my model that has the following columns:
tblProjects[projectName]
tblProjects[projectNumber]
tblProjects[folderName]
tblProjects [folderNumber]
tblProjects[folderStatus]
tblProjects [orderName]
tblProjects [orderNumber]
tblProjects[orderStatus]
tblProjects[suborderName]
tblProjects [suborderNumber]
tblProjects[suborderStatus]

The outcome I want is a drillable list of projectName, folderName, orderName, suborderName in the first column and the associated number in the second column and associated status in the third column.
I have only included my attempt at the second number column because I think that the same approach will work for status if I can find a way to present the number correctly.
It drills properly for me when I have only a single column with all four ‘Name’ columns in the ROWS well but the closest I have gotten to the second column showing the associated number is by using the following measure in the VALUES well:

Item Number =

SWITCH(

TRUE(),

ISINSCOPE(tblProjects[suborderName]), SELECTEDVALUE(tblProjects[suborderNumber]),

ISINSCOPE(tblProjects[orderName]),SELECTEDVALUE(tblProjects[orderNumber]),

ISINSCOPE(tblProjects[folderName]), SELECTEDVALUE(tblProjects[folderNumber]),

ISINSCOPE(tblProjects[projectName]),SELECTEDVALUE(tblProjects[projectNumber]),

BLANK()

)

The issue with this is that as I drill into any particular project, the folderNumber is correct but the parent projectNumber disappears which I understand is the expected behaviour as any parents are no longer in scope but isn't quite what I am after for my matrix visual.

Is there a way to achieve my goal?

[v-pnaroju-msft]

Hi @TasCat09,

Thank you for sharing your PBIX file and for confirming the specific visual behavior you wish to achieve.

The issue you are encountering, where parent-level values (such as projectNumber) disappear during drill-down, is attributable to a limitation in the version of Power BI Desktop you are currently using: Version 2.121.1642.0 (September 2023). This version does not fully support Matrix visuals in preserving parent context when employing DAX logic like ISINSCOPE() and dynamic measures.

To address this issue, kindly upgrade to a newer version of Power BI Desktop, as Microsoft has introduced several enhancements in the Matrix visual behaviour in versions from March 2024 onwards. These improvements include better support for hierarchy rendering, enhanced DAX evaluation across drill levels, and correct context preservation in Matrix visuals. We recommend using Power BI Desktop version March 2024 or later.

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other members of the community who may be facing similar challenges.

Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[TasCat09]

Thank you both for looking into this for me.
@v-pnaroju-msft , I was very excited to see your solution and I can confirm the PBIX file you attached is exactly the behaviour I am looking to achieve. However when I re-crested the same measures and matrix visual in my pbix file, it still didn't show any values for higher than the current levels of the hierachy which is the same behavious as I got with the solution I posted initially as close but not quite
I am not sure why so I have included a link to my PBIX file which was created in Version: 2.121.1642.0 64-bit (September 2023) which is an older version of PBI Desktop that my organisation provides on-prem. Not sure if that is the problem?
https://drive.google.com/file/d/1vxaN1G_8lHEEaD6EQhKBuHgrgqNe_s7Q/view?usp=sharing

[v-pnaroju-msft · Accepted solution]

Thankyou, @Akash_Varuna, for your response.

Hi TasCat09,

We sincerely appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding of the scenario and the drill-down requirements, we have provided a solution using measures that preserve the correct Number and Status at every level of the hierarchy, even when the parent fields go out of scope. The solution employs ISINSCOPE() in combination with MAX() to maintain the appropriate context and is fully drill-compatible within the Matrix visual.

Please find attached the screenshot and the PBIX file for your reference, which we hope will help resolve the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will be beneficial for other community members who may face similar queries.

Should you have any further questions or require additional assistance, please feel free to contact the Microsoft Fabric Community.

Thank you.

[Akash_Varuna]

Hi @TasCat09 Modify the Item Number measure to include parent context dynamically:

Item Number = 
SELECTEDVALUE(tblProjects[suborderNumber], 
    SELECTEDVALUE(tblProjects[orderNumber], 
        SELECTEDVALUE(tblProjects[folderNumber], 
            SELECTEDVALUE(tblProjects[projectNumber])
        )
    )
)

Place the projectName, folderName, orderName, and suborderName in ROWS, and use the measure in VALUES to preserve hierarchy during drill.

[TasCat09]

Thanks for your advice Akash. It is probably because I have implemented it wrong but I had the same issue of the number field being blank for higher levels of the hierachy when I drilled down. I think I have gotten used to the idea that the higher hierachy levels disappear