Hiding repeated or unimportant values in a Matrix?

[jjvowata]

I have this matrix with several "Value" columns:

Is there any way to only show the total at the top for Monthly Salary and Salary Balance, I don't need either of those to show for each row on the matrix, the rest of the columns are ok though.

[Sergii24]

Hi @jjvowata, you need to use INSCOPE() function to change calculation behavior depending on hierarchy level. You can learn more about it here:

• ISINSCOPE – DAX Guide
• Distinguishing HASONEVALUE from ISINSCOPE - SQLBI

Good luck! 🙂

[jjvowata]

I tried the following:

Monthly Salary =
IF (
ISINSCOPE('back_office hr_employees'[Employee]),
'back_office hr_employees'[yearly_salary] / 12,
BLANK()
)

and it returns blank on every single row, even in the "Employee Name" rows, am I missing something?

[v-jtian-msft]

Hi,@jjvowata Hello,@Sergii24,

thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Using the isincope() function is a very good way to do this, similarly you can use the isfiltered() function to achieve a similar effect, they can both be used in situations where you need to change the values displayed in visual.
According to your description, you are using the right function but creating it in the wrong way, you should create the measure instead of the calculate column.
Here is the result of my test:

measure:

M_ = 
IF(
    ISINSCOPE('Table'[Projects]),
    BLANK(),
    DIVIDE(SUM('Table'[Monthy Salary]),12)
    )

calculate column:

C_error = 
IF(
    ISINSCOPE('Table'[Projects]),
    DIVIDE(SUM('Table'[Monthy Salary]),12),
    BLANK()
    )

The correct way is to use measure.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sergii24]

Hi @jjvowata, try to apply INSCOPE() to the lowest level of hierarchy you want the value to be calcualted.

Take a look at the example below:

When you look at project, employee is a part of hierarchy (so it's a part of INSCOPE() ) because you expand the hierarchy to the next level.

InScope Employee = 
    IF(
        ISINSCOPE( 'Table'[Employee] ),
        1,
        BLANK()
    )

 
On the contrary, if we do similar calculation on project, then there are cases when Project is not (yet) a part of actual hierarchy unles you open it (expand to the next level). Therefore, at higher level of hierarchy value remains blank()

InScope Project = 
    IF(
        ISINSCOPE( 'Table'[Project] ),
        1,
        BLANK()
    )

 
I hope it helps 🙂