Filtering a matrix using a measure

[Anonymous]

Hi,

I have a matrix with multiple rows, let's say:

Project

    Sub-project

           Task

                Cost

I need to filter the matrix only for project which have a negative balance (I already have a measure which returns 1 if the balance is negative). However if I try to apply this measure as a filter to the matrix and show only where = 1 it obviously returns all the rows where the balance is negative.

The issue is that I want to return only negative project (so overall a project can be positive even if some of the subproject/task/cost might have a negative balance - I'm not interested in these).

Is there a way to write a measure to return up to the lowest level only if all the levels above are negative (starting from the highest level)?

As an example, for now it's returning Project A and Project B when applying the filter but I only want to see Project B (overall negative):

Thanks!

[v-luwang-msft]

Hi @Anonymous ,

Test like below:

base data:

Then use the below measure:

TEST1 = 
VAR TES1 =
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        ALLEXCEPT ( 'Table', 'Table'[Project] ),
        'Table'[Task] = "Balance"
    )
VAR tes2 =
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        ALLEXCEPT ( 'Table', 'Table'[Project], 'Table'[Subprojiect], 'Table'[Task] ),
        'Table'[Task] = "Balance"
    )
var test3=
    IF ( TES1 < 0 && tes2 < 0, 1, BLANK () )  return IF(HASONEVALUE('Table'[Task]),SUM('Table'[Cost]),test3)

Create matrix  by use the measure:

This is what you want!

Wish it is helpful for you!

Best Regards

Lucien

[Anonymous]

Hi @v-luwang-msft , thanks a lot! We are moving in the right direction. My data are structured in a different way. I have a measure (Balance) which calculates the difference between budget and expenditure and then a measure (Check Balance) which returns the projects/lines which are in negative balance.

I want to use the check balance measure to filter the table:

However I do want to show only ProjectB since it's the one which is negative on aggregate level and I want to see all the subprojects of ProjectB, not only the negative portion:

I'm not sure if this can be achieved though, since in the matrix filters are applied on the lowest level of the hierarchy (so showing only 1 on project level will not do the job because it will return an empty matrix).

[v-luwang-msft]

Hi @Anonymous ,

Could you pls share your pbix file? Need to test it .Rememeber to remove confidential data.

Best Regards

Lucien

[Anonymous]

Hi @v-luwang-msft, can't upload files directly here.

See below a sample I adapted from your pbix file.

I have 2 tables with a 1 to * relationship (filter both direction).

Project:

Table:

Then the measures:

Balance =
SUM ( 'Table'[Budget] ) - SUM ( 'Table'[Expenditure] )

Check Balance =

VAR over =

SUMMARIZE (

'Project';

'Project'[Project];

"Overspend";

CALCULATE (

DISTINCTCOUNT ( 'Project'[Project] );

FILTER ( 'Project'; [Balance] < 0 )

)

)

RETURN

SUMX ( over; [Overspend] )

Thank you!

[v-luwang-msft · Accepted solution]

Hi  @Anonymous ,

Try measure like below:

TESS = 
IF (
    HASONEVALUE ( 'Table'[Subprojiect] ),
    IF (
        HASONEVALUE ( 'Table'[Task] ),
        IF (
            SUMX (
                FILTER ( ALL ( 'Table' ), 'Table'[Project] = MAX ( 'Table'[Project] ) ),
                'Table'[Budget] - 'Table'[Expenditure]
            ) < 0
                && SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Task] = MAX ( 'Table'[Task] )
                            && 'Table'[Project] = MAX ( 'Table'[Project] )
                            && 'Table'[Subprojiect] = MAX ( 'Table'[Subprojiect] )
                    ),
                    'Table'[Budget] - 'Table'[Expenditure]
                ) < 0,
            1,
            BLANK ()
        ),
        IF (
            SUMX (
                FILTER ( ALL ( 'Table' ), 'Table'[Project] = MAX ( 'Table'[Project] ) ),
                'Table'[Budget] - 'Table'[Expenditure]
            ) < 0
                && SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Project] = MAX ( 'Table'[Project] )
                            && 'Table'[Subprojiect] = MAX ( 'Table'[Subprojiect] )
                    ),
                    'Table'[Budget] - 'Table'[Expenditure]
                ) < 0
                && SUMX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Task] = MAX ( 'Table'[Task] )
                            && 'Table'[Project] = MAX ( 'Table'[Project] )
                            && 'Table'[Subprojiect] = MAX ( 'Table'[Subprojiect] )
                    ),
                    'Table'[Budget] - 'Table'[Expenditure]
                ) < 0,
            1,
            BLANK ()
        )
    ),
    IF (
        SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[Project] = MAX ( 'Table'[Project] ) ),
            'Table'[Budget] - 'Table'[Expenditure]
        ) < 0,
        1,
        BLANK ()
    )
)

And if you want to show only ProjectB since it's the one which is negative on aggregate level and I want to see all the subprojects of ProjectB, not only the negative portion, try the following measure:

TESS1 = 
    IF (
        SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[Project] = MAX ( 'Table'[Project] ) ),
            'Table'[Budget] - 'Table'[Expenditure]
        ) < 0,
        1,
        BLANK ()
    )

Final get the below:

Wish it is helpful for you!

Best Regards

Lucien

[Anonymous]

Hi @ebeery , thanks for your reply. I actually already tried this solution. However I need to return "1" for all the lines down in the hierarchy for negative projects since when you apply a filter in a matrix it's being evaluated on the lowest level (i.e. for Project B I would like to get all the subprojects) and not on the project line.

I think should be something related to https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/ but I couldn't figure it properly.

[ebeery]

Hi @Anonymous take a look at the ISINSCOPE() function.

I think you should be able to do something like IF(ISINSCOPE([Project Column]), [Measure]) to return your "1" measure only on the Project lines of the hierarchy.