Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Report filter not working with DAX Calculation

Hi all,

 

I would like some help with this one please. I have the below DAX calculation and it is working fine:

 

TEST Custom Sales =
CALCULATE (
      [Sales],
      FILTER (
          SalesFact,
          RELATED('Date'[Financial Year]) = Max( 'Date'[Financial Year] )
          )
)


The issue is when I select a value from a report filter, for example Product = "Shoes", the calculation returns (Blank).. 

 

On the other hand when i specify a fixed value in the filter, the  product filter works fine:

 

TEST Custom Sales =
CALCULATE (
      [Sales],
      FILTER (
          SalesFact,
          RELATED('Date'[Financial Year]) = 2020
          )
)

 

Tables Date, Product, SalesFact are linked properly so there is no issues with the model. 

 

Can anyone help me resolve this problem?

13 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi.

       

      I updated the main topic. 

      There are no relationship issues in the model,. As per main topic the calculation seems to work fine when a fixed value is used in the calculation?

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    What is this measure trying to achieve?

    By the way , filtering by a full (expanded) table is a very dangerous and bad idea as you will learn very soon when you start creating other measures based on this one.

    Best
    D
    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks for your message.

       

      The idea behind the calculation is to calculate total sales for financial periods. Sadly i cannot use conventional calendar calculations hence have to do it this way.

       

      Any suggestion on how to accomplish this?

      • Anonymous's avatar
        Anonymous
        Not applicable
        What about other products when you filter by them? Does this affect all of them?

        Best
        D