Forum Discussion

asjones's avatar
asjones
Helper V
7 years ago
Solved

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

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

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    7 years ago

    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's avatar
      asjones
      Helper V
      7 years ago

      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!!