none
DAX with SUMMARIZE and Cross Table Filtering

    Question

  • The title of this may not be exactly what I am looking for so I will describe the objective first.


    I have a table with unique instances of cases named 'InvestigationCase'
    I want to have counts of cases aggregated by EscalatedDate and ResolutionDate within the same pivot. 
    For instance all cases that were escalated and resolved by month or year, etc.

    There are 2 sepearte timeintelligence dimensions for both Escalateddate and ResolutionDate that have relationships
    to the InvestigationCase table.

    I created a 3rd timeintelligence dimension named InvestigationCaseAggregateDate that I thought could be used for creating the DAX calculated measures
    that would aggregate the case counts. The DateKey in this dimensin is the key

    I was able to create a relationship between 'InvestigationCase'[ResolutionDateKey2] and InvestigationCaseAggregateDate[DateKey]
    When I create a relathionship between 'InvestigationCase'[EscalationDateKey2] and InvestigationCaseAggregateDate[DateKey] it makes it inactive
    my assumption is that I cant have 2 different relationships to the same lookup table.

    I tried various approaches to working around not having a relationship but no matter what I do it wont let me create the measure without a relationship.  I tried USERELATIONSHIP and it
    has no effect.

    I was leveraging some dax examples from this link
    http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering

    and tried some of this as well

    http://sqlblog.com/blogs/marco_russo/archive/2010/02/09/how-to-relate-tables-in-dax-without-using-relationships.aspx

    based on the examples I cread measure1 in the InvestigationCaseAggregateDate table. This seems to work and returns th correct number allows me to filter and slice it by various time dimension attributes

    Measure1:=COUNTAX( 'InvestigationCase',CALCULATETABLE( VALUES( 'InvestigationCase'[CaseID] ),
              FILTER( 'InvestigationCaseAggregateDate','InvestigationCaseAggregateDate'[DateKey] = 'InvestigationCase'[EscalationDateKey2] ) ) )

    When I create measure 3 below I thought it should do the same for the aggregated resolution date counts but it will not work.  It dosent error it just dosent return many of the cases that should be included in the counts. 
    Its almost like its trying to filter within the filter thats already in effect.  I tried various options like CALCULATE TABLE instead of CALCULATE and still no luck.

    Measure3:=COUNTAX( 'InvestigationCase', CALCULATETABLE( VALUES( 'InvestigationCase'[CaseID] ),
               FILTER( 'InvestigationCaseAggregateDate', 'InvestigationCaseAggregateDate'[DateKey] = 'InvestigationCase'[ResolutionDateKey2] ) ) )

    I am open to other ways of doing this,  I dont know if my idea is the best way.  I just thought having a single acting as a time dimesnion with aggregate measures at teh day levelk would make reporting easier .

    I have tried a lot of different approaches and worry this is not possible or I am just overcomplicating something that is simple.  If only te relationships would cooperate this would be easy.


    Jim

    Thursday, September 26, 2013 2:32 AM

Answers

  • Hi Jim,

    those two calculations work just fine for me

    CntResolution:=CALCULATE(COUNTROWS('Case'), 
    USERELATIONSHIP(
        'Case'[ResolutionDate], 
        AggregateDateDim[AggregateDate]))
    
    CntEscalation:=CALCULATE(COUNTROWS('Case'), 
    USERELATIONSHIP(
        'Case'[EscalationDate], 
        AggregateDateDim[AggregateDate]))

    can you check them and tell us if the provide the expected values?

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by CodeManSeattle Tuesday, October 01, 2013 4:49 AM
    Monday, September 30, 2013 8:35 PM
    Answerer

All replies

  • Jim,

    it would be helpful if you provide a screenshot of the diagram view or a simple repro of the issue in a workbook that you can share. Moreover, it would be helpful having a sample workbook with the result you obtain from your measure and the expected result side-by-side. Otherwise, the short time available (at least to me) is spent trying to understand the issue and creating a repro, and if this requires too much time I end up not having time to answer! :)

    Thanks,

    Marco


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

    Thursday, September 26, 2013 4:52 PM
  • Hi Marco - I understand and apologize that I didn't provide enough info. I really appreciate you taking the time to respond back!  My original solution had PII data and is built in Analysis Services so I didn't have a PowerPivot workbook to upload.  I spent some time yesterday created a simple PowerPivot workbook focused on exactly the subject of my question with some sample data.

    Below is the link to the Excel 2013 PowerPivot Workbook on my SkyDrive.  Let me know if your not able to access it or if I can provide anything that will make it easier to see where I am trying to go.

    https://skydrive.live.com/redir?resid=E15EED259425C9B3!920&authkey=!APU1HvxIPkjl-4M

    Ultimately I want to be able to produce a pivot table report similar to the pivot table example I include din the workbook.  Giving the user the ability to filter on a specific time period and show the two measures aggregated by attributes of the AggregateDateDim time dimension.

    SAs you will see the Escalationdate relationship from Case table to the AggregateDateDim table is not active.  I understand about the limitations on relationships and why only one of the relationships is active.  I believe that is part of the issue and the other is probably me msunderstanding the filter. 

    Only the measure that involves the active relationship works. I thought from reading on of the  examples in my first post that there was a way to create the measures without needing a relationship which was how I was trying to work around the relationship issue.  I tried the USERELATIONSHIP function so that it would force it but with no success.

    As I mentioned in my first post,  I am open to alternate approaches.  I just need to keep the ability on the case table to still have the relationships to the EscalationdateDim and ResoloutionDateDim so that users can still go after specific around those dates but also need a method that allows for aggregating the counts across the 2 periods using the  AggregateDateDim

    Below are the 2 measures. 

    CountResolution:=COUNTAX( 'Case', CALCULATETABLE( VALUES( 'Case'[Ticket] ), FILTER( 'AggregateDateDim','AggregateDateDim'[AggregateDate] = 'Case'[ResolutionDate] ) ) )

    CountEscalation:=COUNTAX( 'Case', CALCULATETABLE( VALUES( 'Case'[Ticket] ), FILTER( 'AggregateDateDim','AggregateDateDim'[AggregateDate] = 'Case'[EscalationDate] ) ) )


    Jim


    Saturday, September 28, 2013 2:32 PM
  • Hi Jim,

    those two calculations work just fine for me

    CntResolution:=CALCULATE(COUNTROWS('Case'), 
    USERELATIONSHIP(
        'Case'[ResolutionDate], 
        AggregateDateDim[AggregateDate]))
    
    CntEscalation:=CALCULATE(COUNTROWS('Case'), 
    USERELATIONSHIP(
        'Case'[EscalationDate], 
        AggregateDateDim[AggregateDate]))

    can you check them and tell us if the provide the expected values?

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by CodeManSeattle Tuesday, October 01, 2013 4:49 AM
    Monday, September 30, 2013 8:35 PM
    Answerer
  • Thank you Gerhard, your a genius:)  I made it more complicated than it needed to be.

    Jim

    Tuesday, October 01, 2013 4:50 AM
  • Nice to know Gerhard solved it!

    Marco

    Tuesday, October 01, 2013 9:43 PM