locked
Application of Filter() in DAX RRS feed

  • Question

  • I am currently experimenting with DAX calculations and have come around a challenge for which I would appreciate input from experts: I have a model that is supposed to calculate changes over time (year to year). The Start and end year can be flexibily selected by the user. The model contains the main table which contains incidents of crime a date table and simple linked tables for start and end year ("Comparison" and "End").

    The calculations are defined as follows:

    Incidents%Start:=CALCULATE([Incidents %],filter(date,'Date'[Year]=[Base])) Incidents%End:=CALCULATE([Incidents %],filter(date,'Date'[Year]=[End])) Relative Change Incidents %:=DIVIDE(([Incidents%End]-[Incidents%Start]),[Incidents%Start])

    Base:=if(countrows(Comparison)=1,sum(Comparison[Base Year]),BLANK())

    End:=if(countrows('End')=1,sum('End'[End Year]),BLANK())

    This all works fine as long as I don't change the context by selecting a specific year from the date table then the results becomes blank which I don't quite understand as I would expect the FILTER() functions to overrule the "Year" selected in the context. I am pretty sure this is not a bug but a misunderstanding on my part but simply can't -after extensive tries and experimenting with different approaches- find an answer to this.

    working fine

    not working


    • Edited by Karaoan Tuesday, September 24, 2013 12:24 AM
    Monday, September 23, 2013 11:43 PM

Answers

  • Hi Karaoan,

    I don't see your data model. But I guess the measure Incidents%Start & Incidents%End effected by the filter context of Calendar. You can try to add All formulas for date table as below :

    Incidents%Start:=CALCULATE([Incidents %],filter(All(date),'Date'[Year]=[Base])) Incidents%End:=CALCULATE([Incidents %],filter(ALL(date),'Date'[Year]=[End]))

    Regards,

    • Marked as answer by Karaoan Tuesday, September 24, 2013 8:09 PM
    Tuesday, September 24, 2013 3:22 PM

All replies

  • Hi Karaoan,

    I don't see your data model. But I guess the measure Incidents%Start & Incidents%End effected by the filter context of Calendar. You can try to add All formulas for date table as below :

    Incidents%Start:=CALCULATE([Incidents %],filter(All(date),'Date'[Year]=[Base])) Incidents%End:=CALCULATE([Incidents %],filter(ALL(date),'Date'[Year]=[End]))

    Regards,

    • Marked as answer by Karaoan Tuesday, September 24, 2013 8:09 PM
    Tuesday, September 24, 2013 3:22 PM
  • Thanks so much for your help CDzung that was the answer!
    • Edited by Karaoan Tuesday, September 24, 2013 8:12 PM
    Tuesday, September 24, 2013 8:12 PM