locked
DAX FIRSTDATE in a filter context - Contoso example RRS feed

  • Question

  • Hello,

    I'm probably missing something basic... In Contoso PowerPivot I try to get the first date for all kinds of slices (Month, Product, Store etc.)

    I 've marked DimDate as a date table, Datekey as a Date. I've written this measure:

    TestFirstDate:=FIRSTDATE(DimDate[Datekey])

    It works OK if I put on PivotRows CalendarMonth from DimDate table

    BUT

    It showed me ALL the months from DimDate, regardless of actual Sales... Well, to overcome this I've made a calculated column in FactSales

    =FORMAT(FactSales[DateKey],"YYYY-MM") - so it will represent SalesMonth for Actual sales

    BUT when I put this column on rows instead of CalendarMonth, the TestFirstDate measure shows me 01/01/2005 regardless of SalesMonth

    I'm confused, it's like it's unaware of the context ...

    Please help - what am I missing?

    It's gotta be something basic and simple

    Thank you in advance

    Michael

    Row Labels Sum of SalesAmount TestFirstDate
    2007-01 $269,835,263.23 01/01/2005 00:00
    2007-02 $298,215,968.35 01/01/2005 00:00
    2007-03 $300,486,926.90 01/01/2005 00:00
    2007-04 $400,160,331.60 01/01/2005 00:00
    2007-05 $423,429,127.79 01/01/2005 00:00
    2007-06 $409,797,545.55 01/01/2005 00:00
    2007-07 $389,617,372.27 01/01/2005 00:00
    2007-08 $388,429,827.11 01/01/2005 00:00


    Michael

    Friday, March 8, 2013 12:10 PM

Answers

All replies

  • The filter context does not propagate from many-to-one (from sales to date in this case).
    In order to do that, you have to use the same many-to-many pattern described here:

    http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/

    Try writing your formula in this way:

    TestFirstDate:=CALCULATE( FIRSTDATE(DimDate[Datekey]), FactSales )


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by M. Shparber Friday, March 8, 2013 10:25 PM
    Friday, March 8, 2013 5:11 PM
  • ... And be sure to study this, and Marco's, post about time intelligence functions: http://mdxdax.blogspot.com/2011/01/dax-time-intelligence-functions.html  There are behind-the-scenes things going on with TI functions that I could never have sorted out without their help.

    bob mick

    Friday, March 8, 2013 6:57 PM
  • Thanks Marco,

    It is working now

    Just puzzled by CALCULATE(........, FactSales)

    Does it go smth like :"Notwithstanding that filter context does not propagate from many-to-one, I want you to find the first date from DimDate for all records from FactSales in the context USING THE RELATIONSHIP"?

    In other words, the context IS applied to "FactSales" filter because of CALCULATE and then filtering of DimDate is done by existing relationship?

    I'm trying to get the intuition behind it :)

    Thanks again!

    Michael Shparber


    Michael

    • Proposed as answer by Marco Russo Friday, March 8, 2013 10:57 PM
    Friday, March 8, 2013 10:35 PM
  • Almost... :-)

    CALCULATE applies to the first argument expression all the filters contained in the following arguments, which are filters.
    By passing a table as a filter to CALCULATE, all the columns of the table for the rows that are active in the filter context are applied as a filter.
    If the table has many-to-one relationships with other tables, all the columns of the related tables are part of the filter, too.
    Thus, by passing FactSales as second argument, all the columns of all the related tables (i.e. dimensions, such as DimDate) are passed as filter.
    Now, what happens is the following:

    1. You put a filter on the calculated column in FactSales
    2. The rows filtered in FactSales have related rows in DimDate
    3. Passing FactSales as second argument to CALCULATE, all the columns of DimDate are implicitly part of the filter, too - but only days with at least one sale will be part of this filter
    4. This filter restricts the values considered by FIRSTDATE - only days that contains at least one sale are considered in FIRSTDATE evaluation

    The key is understanding the way filter context and row context interacts! :)


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Friday, March 8, 2013 11:12 PM
  • Very nice,

    Thanks Marco


    Michael

    Friday, March 8, 2013 11:16 PM