locked
How to override a date relationship RRS feed

  • Question

  • Hey There - I have a fact table with a relationship to a Calendar dimension based on a date. The fact table also has a score value with a start/end date for when the score is valid.

     ID     Date          Score ScoreStartDate ScoreEndDate
     1726 2/25/2016  4       2/25/2016        4/27/2016
     1726 2/25/2016  8       4/27/2016        5/12/2016
     1727 5/12/2016  4       2/25/2016        4/27/2016
     1727 5/12/2016  8       4/27/2016        5/12/2016

    What I want is a measure that will count the distinct ID values for a given score each month, ie from the above data, I want to produce this:

     FiscalMonth Score Count
     2016-02     4        2
     2016-03     4        2
     2016-04     4        2
     2016-04     8        2
     2016-05     8        2

    The problem is that I'm getting this result, because the date relationship is active and each row only maps to a single month:

     FiscalMonth Score Count
     2016-02      4       1
     2016-05      8       1


    The measures I've tried are all variations of this:

     ScoreTimeLineCount:=CALCULATE
     (
      distinctcount('FactTable'[OpportunityId]),
      all('Calendar'),
      filter
      (
       'FactTable',
       (
        'FactTable'[ScoreStartDate] >= FIRSTDATE('Calendar'[Date]) &&
        'FactTable'[ScoreStartDate] <= LASTDATE('Calendar'[Date])
       ) ||
       (
        'FactTable'[ScoreEndDate] >= FIRSTDATE('Calendar'[Date]) &&
        'FactTable'[ScoreEndDate] <= LASTDATE('Calendar'[Date])
       ) ||
       (
        'FactTable'[ScoreEndDate] < FIRSTDATE('Calendar'[Date]) &&
        'FactTable'[ScoreEndDate] > LASTDATE('Calendar'[Date])
       )
      )
     )

    But I can seem to get them to work, and i've verified that if I remove the relationship with the Calendar dimension, it works fine.

    Can someone explain what I'm missing? I've used the ALL function in numerous other cases and made it work, I just can't get this one to work.

    Thanks, Dean


    Got a bug or feature request? Tell me about it at http://connect.microsoft.com. This posting is provided "AS IS" with no warranties, and confers no rights.


    • Edited by Dean Kalanquin Saturday, September 10, 2016 4:09 AM Fixed example
    Saturday, September 10, 2016 4:06 AM

Answers

  • Hi Dean,

    This measure was a bit of a doozy!

    Here's the DAX code for one possible measure that should do the trick, partly modelled on the 'events in progress' measure in this paper (page 27):
    http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

    =
    CALCULATE (
        CALCULATE (
            DISTINCTCOUNT ( FactTable[OpportunityId] ),
            ALLEXCEPT ( FactTable, FactTable[OpportunityId] )
        ),
        GENERATE (
            CALCULATETABLE (
                SUMMARIZE (
                    FactTable,
                    FactTable[OpportunityId],
                    FactTable[ScoreStartDate],
                    FactTable[ScoreEndDate]
                ),
                ALL ( 'Calendar' )
            ),
            CALCULATETABLE (
                VALUES ( 'Calendar'[Date] ),
                DATESBETWEEN (
                    'Calendar'[Date],
                    FactTable[ScoreStartDate],
                    FactTable[ScoreEndDate]
                ),
                VALUES ( 'Calendar'[Date] )
            )
        )
    )
    

    I have uploaded an Excel 2013/2016 PowerPivot spreadsheet here, demonstrating the measure:

    https://www.dropbox.com/s/t1ct0n82sc8s49t/How%20to%20Override%20a%20Date%20Relationship.xlsx?dl=1

    To explain it briefly:

    1. Within GENERATE, the first CALCULATETABLE argument creates a table summarizing OpportunityId, ScoreStartDate and ScoreEndDate, with Calendar filters removed.
    2. Within GENERATE, the second CALCULATETABLE argument creates (for each row of the summarized table from step 1) a list of dates from ScoreStartDate to ScoreEndDate, intersected with the current Calendar filter context.
    3. The table resulting from GENERATE then contains (among other columns) all OpportunityId values for which the dates from ScoreStartDate to ScoreEndDate intersect the current filter context.
    4. This table is used as a SetFilter argument for the outer CALCULATE.
    5. The outer CALCULATE's first argument is
      CALCULATE ( DISTINCTCOUNT ( FactTable[OpportunityId] ), ALLEXCEPT ( FactTable, FactTable[OpportunityId] )
      which returns the distinct count of OpportunityId values, within the context of the table returned by GENERATE, but ignoring all filters except OpportunityId itself (which will be just the OpportunityId values whose date range intersected the current filter context).


    Owen Auger, CFA https://nz.linkedin.com/in/owenauger


    Saturday, September 10, 2016 2:17 PM

All replies

  • What you're missing, is that filter arguments in CALCULATE do not impact each other. In your case, ALL(Calendar) removes the filter on the Calendar table for the calculation of DISTINCTCOUNT(FactTable[OpportunityID]), but the other filter argument (the FILTER over FactTable) filters the fact table that is still filtered by the Calendar table.

    Your issue will probably be solved by using FILTER(ALL(FactTable), ...) instead.

    From a modeling perspective, don't know what measures you have other than this one but in this case it may be a good idea to make the relationship to Calendar inactive and only enable it when explicitly needed using USERELATIONSHIP.

    I'm not getting what's inside the FILTER, BTW; I'd guess something like

    FILTER(ALL(FactTable),

       FactTable[ScoreStartDate]<=MAX(Calendar[Date] && FactTable[ScoreEndDate]>=MIN(Calendar[Date])

    )

    would do the trick?

    Saturday, September 10, 2016 8:41 AM
    Answerer
  • Hi Dean,

    This measure was a bit of a doozy!

    Here's the DAX code for one possible measure that should do the trick, partly modelled on the 'events in progress' measure in this paper (page 27):
    http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

    =
    CALCULATE (
        CALCULATE (
            DISTINCTCOUNT ( FactTable[OpportunityId] ),
            ALLEXCEPT ( FactTable, FactTable[OpportunityId] )
        ),
        GENERATE (
            CALCULATETABLE (
                SUMMARIZE (
                    FactTable,
                    FactTable[OpportunityId],
                    FactTable[ScoreStartDate],
                    FactTable[ScoreEndDate]
                ),
                ALL ( 'Calendar' )
            ),
            CALCULATETABLE (
                VALUES ( 'Calendar'[Date] ),
                DATESBETWEEN (
                    'Calendar'[Date],
                    FactTable[ScoreStartDate],
                    FactTable[ScoreEndDate]
                ),
                VALUES ( 'Calendar'[Date] )
            )
        )
    )
    

    I have uploaded an Excel 2013/2016 PowerPivot spreadsheet here, demonstrating the measure:

    https://www.dropbox.com/s/t1ct0n82sc8s49t/How%20to%20Override%20a%20Date%20Relationship.xlsx?dl=1

    To explain it briefly:

    1. Within GENERATE, the first CALCULATETABLE argument creates a table summarizing OpportunityId, ScoreStartDate and ScoreEndDate, with Calendar filters removed.
    2. Within GENERATE, the second CALCULATETABLE argument creates (for each row of the summarized table from step 1) a list of dates from ScoreStartDate to ScoreEndDate, intersected with the current Calendar filter context.
    3. The table resulting from GENERATE then contains (among other columns) all OpportunityId values for which the dates from ScoreStartDate to ScoreEndDate intersect the current filter context.
    4. This table is used as a SetFilter argument for the outer CALCULATE.
    5. The outer CALCULATE's first argument is
      CALCULATE ( DISTINCTCOUNT ( FactTable[OpportunityId] ), ALLEXCEPT ( FactTable, FactTable[OpportunityId] )
      which returns the distinct count of OpportunityId values, within the context of the table returned by GENERATE, but ignoring all filters except OpportunityId itself (which will be just the OpportunityId values whose date range intersected the current filter context).


    Owen Auger, CFA https://nz.linkedin.com/in/owenauger


    Saturday, September 10, 2016 2:17 PM