Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering a matrix using a measure

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!

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous , 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.

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-luwang-msft
        Community Support
        5 years ago

        Hi Anonymous ,

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

         

        Best Regards

        Lucien