Forum Discussion

wwenw1's avatar
wwenw1
New Member
1 year ago
Solved

Matrix

In a Power BI matrix visual, how can I keep the values hidden or blank at higher (collapsed) levels, so that values only appear when a user expands a row to see the lower-level details? When I use IS...
Need Help
  • v-tejrama's avatar
    v-tejrama
    1 year ago

    Hi wwenw1,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    To show values in a Power BI matrix only when a user expands to a lower-level detail, you can use the ISINSCOPE() function inside a measure to dynamically control visibility.

     

    If your hierarchy is Category → Subcategory → Product, and you want values to appear only at the Product level.

     

    Here is the DAX : Visible Sales

     

    IF (
    ISINSCOPE(SalesData[Product]),
    SUM(SalesData[Sales]),
    BLANK()
    )

     

    Use this measure in the matrix’s Values field instead of the raw sales column. This will ensure that higher levels (Category and Subcategory) show blank values when collapsed, and only show data when the user drills down to Product.

     

    If you instead want values to be shown at both the Subcategory and Product levels, you can modify the measure like this:

     

    Here is the DAX : Visible Sales Alt

     

    IF (
    ISINSCOPE(SalesData[Subcategory]) || ISINSCOPE(SalesData[Product]),
    SUM(SalesData[Sales]),
    BLANK()
    )

     

    Please find the attached pbix file for your reference.

     

    If the response has addressed your query, please "Accept it as a solution" and give a 'Kudos' so other members can easily find it.

     


    Best Regards,
    Tejaswi.
    Community Support

     

Elena_Kalina's avatar
Elena_Kalina
Solution Sage
1 year ago

Hi wwenw1 

If ISINSCOPE() is causing issues with relationships:

  1. Make sure your model relationships are correctly configured.

  2. Check for circular dependencies (where a measure or calculation refers back to itself in a loop).

  3. Try using TREATAS() to preserve the correct relationship context.

  4. Consider storing intermediate calculations in variables (using VAR) to simplify logic.

For your needs, you could also try using PATH functions (useful for parent-child hierarchies).

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.