Forum Discussion

JimOnABike's avatar
JimOnABike
Regular Visitor
1 year ago
Solved

Same Filters - Different Behaviour

Hi guys,

 

I built the following DAX measure, which gives the total revenue for the current year, irrespective of filters applied:

 

TotalRevCurrentYear = CALCULATE(SUM(Revenue[Revenue]),
FILTER(ALL('Date'),'Date'[year] = MAXX(ALL('Date'),'Date'[year])))
 
It behaves exactly as planned. I then built a second measure, with exactly the same filters, but using Units sold:
 
TotalUnitsCurrentYear = CALCULATE(SUM(Revenue[Units_Sold]),
FILTER(ALL('Date'),'Date'[year] = MAXX(ALL('Date'),'Date'[year])))
 
But with this, as I click on dimension in the report, the values of the measure change...
 
Porting in from a different BI environment, so any help getting to grips with understanding PBI intereactions and behaviours greatly appreciated.
 
Need Help
  • JimOnABike's avatar
    JimOnABike
    1 year ago

    Guys - I had allocated the wrong measure to the Card!

     

    It's now working as expected thanks for your time.

     

    In my defence - I'm about 3 hours into this and have a few measures starting "TotalRevenue*"  

     

6 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi JimOnABike  To get exact result, it is better to provide representative data and desired output. It is not clear, what is changing actually. Since, ALL removes all filters from the specified table, but if there are other filters or slicers applied to the 'Revenue' table, they might still affect the measure. So, to include only current year, you could try this filter:

     

    FILTER(
        ALL('Date'),
        'Date'[year] = YEAR(TODAY())
    )

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution

     

    Best Regards,
    Shahariar Hafiz

    • JimOnABike's avatar
      JimOnABike
      Regular Visitor
      1 year ago

      Thanks for coming back on this. Expected behaiour (wanted) is for both card values to be unaffected by a change in filter. Here I have filtered to 2020 (the MAX year in the dataset). Revenue is 2.5bn, Units Sold is 476:

       

      Now if I change the  filter (top-left) to 2019...

      Total Number of Units has changed, but Total Revenue is unaffected.

      I do want selections made to country and product dimensions to change the card values, and they do. What I don't understand is why two measures from the same table, using the same filter() are giving different behaviour...

       

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi JimOnABike 

     

    Did you click on dimensions other than from the Date table? If so, that is expected as the filter modifier is applied to that table only.

  • JimOnABike's avatar
    JimOnABike
    Regular Visitor
    1 year ago

    Guys - I had allocated the wrong measure to the Card!

     

    It's now working as expected thanks for your time.

     

    In my defence - I'm about 3 hours into this and have a few measures starting "TotalRevenue*"  

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Just select the current year in the slicer and these 2 measures should work

    Rev = sum(Revenue[Revenue])

    Units = sum(Revenue[Units_sold])

    Hope this helps.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi JimOnABike ,

     

    It looks like your problem has been solved, please mark the helpful reply and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .
    Thank you very much for your kind cooperation!

    Best Regards,
    Dengliang Li