none
All rows show the same data value even if relationships are set.

    Question

  • Hello;

    I’m new to PowerPivot so please bear with me as I ask what might be a simplistic question of the pros.

    I have a model with 4 tables in it. One is a Time Table (T1).

    I have two measures (Incident count and Surveys Sent) which are in one table (T2) and a third measure Surveys Returned which is in the third table (T3). I have relationships setup between T1 and T2 on the T1.DateKey and T2.ReportedDate and another one between T1.DateKey and T3.DateEntered. There is another relationship between T2.ITGroup and T4.ID.

    Now, in a Pivot Table, if I put either T1.Date or T2.ITGroup in the Row Labels and then always put T2.IncidentCount, T2.SurveysSent and T3.SurveysReturned in the Values field, what I get is T2.IncidentCount and T2.SurveysSent are broken down as expected either by date or ITGroup depending on which one I put in the Rows Label, but T3.SurveysReturned always returns the same value for every rows.

    This is kind of driving me nuts. I’ve seen this in Multi-Dim SSAS and I know how to fix it, but PowerPivot and SSAS Tabular – Well, it’s a mystery. So absolutely ANY help on this would be greatly appreciated.

    I hope this makes sense to you.

    Cheers.


    Thanks Ron...

    Wednesday, September 18, 2013 1:20 PM

Answers

  • Ron, 

    The current restriction in Power Pivot is that there cannot be relationship cycles. Like Javier mentions, if the relationship between T1 and T3 in my picture above was "flipped" (i.e. T3 is the lookup, the arrow points towards T3) then creating a relationship T3->T2 would create a cycle T1->T3->T2->T1 which is not allowed. In the event that you needed this, you would have to make one of the relationships in the cycle inactive, then use the USERELATIONSHIP function in your measures to get the desired behavior.

    Thank you,

    Taylor Clark

    Thursday, September 19, 2013 4:43 PM

All replies

  • Ron, 

    Based on your description, your schema looks something like this:

    I would expect a pivot table like this to give you the proper values:

    Rows: T1.DateKey

    Values: T2.IncidentCount, T2.SurveysSent, T3.SurveysReturned

    While the following table would give you all the same number for T3.SurveysReturned:

    Rows: T2.ITGroup or T4.ID

    Values: T2.IncidentCount, T2.SurveysSent, T3.SurveysReturned

    This is because there is no relationship between T3 and T2 or T3 and T4. 

    Is the above behavior correct? If not, could you post your measure definitions? 

    Thank you,

    Taylor Clark


    Wednesday, September 18, 2013 8:20 PM
  • Hi Taylor;

    Thanks for getting back to me. I did indeed have the exact same relationships that you indicate above and it didn't work. That being said, I added another relationship betwwen T2 & T3 on another field and that seems to have done the trick.

    Another question though, I couldn't add that relationship in PowerPivot because it said there was already a relationship between T3 and T1 so I couldn't add a second relationship from T3 to T2. I could however do it in SSDT in my Tabular Model and that's how I fixed my problem. I was using PowerPivot as a quick & dirty test bech to debug my model. How come we can't create multiple relationships in PowerPivot?

    Anyway, thanks a milion for the help.

    Cheers.


    Thanks Ron...

    Thursday, September 19, 2013 1:38 PM
  • From his description, perhaps the only difference with the model Taylor posted is in the relationship between T1 and T3.  On Taylor's model, T1 is still the lookup table, but from the original poster's message it appears his model has T3 as the lookup.  If this is the case, neither T1.Date or T2.ITGroup will give a correct value for T3.SurveysReturned.

    As DAX propagates filter context by default following the inverse direction of the arrow in the model -

    T1 can slice T2 if T1 is the lookup

    T2 can slice itself

    but T1 won't be able to slice T3 if T3 is the lookup

    This assumes the measures refer to columns on the tables in which they are allocated




    Javier Guillen
    http://javierguillen.wordpress.com/

    Thursday, September 19, 2013 1:50 PM
  • Ron, 

    The current restriction in Power Pivot is that there cannot be relationship cycles. Like Javier mentions, if the relationship between T1 and T3 in my picture above was "flipped" (i.e. T3 is the lookup, the arrow points towards T3) then creating a relationship T3->T2 would create a cycle T1->T3->T2->T1 which is not allowed. In the event that you needed this, you would have to make one of the relationships in the cycle inactive, then use the USERELATIONSHIP function in your measures to get the desired behavior.

    Thank you,

    Taylor Clark

    Thursday, September 19, 2013 4:43 PM