Filter table based on Calculated Measure(s), using DAX formulas

[asjones]

I have the following DAX measure that I heavily simplified down to the issue.

Expense =

    VAR _Amount =

    CALCULATE(

        SUM ( Financial[Amount4] ) ,

              ProjectMasterFile[Project_Type] >= 50 

          )

    Return _Amount

If I create a table with columns:

ProjectMasterFile[Project], ProjectMasterFile[Project_Type], Expense

Then the Expense Measure comes out correct.

If I change the table to

ProjectMasterFile[Project_Type], Expense

Then the expense measure repeats the same value for all rows.

The original DAX has several other filters and they all worked and did not have repeated values if I did not have this line

ProjectMasterFile[Project_Type] >= 50 

The relations in the data model look correct. So what might I be doing wrong?

Any and all ideas are appreciated.

Thanks

Alan

[OwenAuger · Accepted solution]

@asjones 

Filter arguments within CALCULATE normally overwrite existing filters.

A consequence of this is that if one of the filter arguments overwrite filters on columns that are included within your visual, you will see the same value returned for each value of that column within the visual.

You can modify the behaviour of filter arguments so that they instead intersect with existing filters using KEEPFILTERS - possibly this will give you the value you expect.

Try rewriting your measure as:

Expense =
    VAR _Amount =
    CALCULATE(
        SUM ( Financial[Amount4] ) ,
              KEEPFILTERS ( ProjectMasterFile[Project_Type] >= 50 )
          )
    Return _Amount

Does this give the expected result? Please post backif needed.

Regards,

Owen

[asjones]

Owen,

Thanks for your response. You are right .... i have used KEEPFILTERS() before, but only when I had something in the filters pane. I had never thought of Fitler Context for a visual (need to rewatch the related videos.

Thanks!!