locked
PowerPivot Bridge Tables RRS feed

  • Question

  • I have 3 tables: Customer, Address, CustomerAddress (i.e. bridge table).  What I want to do is perform a pivot table breakdown by the number of customers per State (State comes from the Address).  However, I cannot figure out how to make this work with the CustomerAddress bridge table.  I believe this has do be done using a DAX expression (which seems rather ridiculous and limited). I'm new at PowerPivot, but a seasoned SQL programmer, so being stuck on such a trivial item is getting really frustrating! Any help would be appreciated.
    Wednesday, April 9, 2014 5:21 PM

Answers

  • I've double checked too in a simple model similar to the setup that Murali has except modelling it as a many to many and using my calc expression and it works fine for me. Are you saying that you still getting repeating values in your pivot tables?

    Are your relationships setup to look like the following?

     Customer <---  CustomerAddress ---> Address

    I'm assuming that because you have a bridge one Customer can have multiple addresses or one address can have multiple customers.


    http://darren.gosbell.com - please mark correct answers


    • Edited by Darren GosbellMVP Friday, April 11, 2014 1:29 AM clarifying m2m scenario
    • Proposed as answer by Michael Amadi Friday, April 11, 2014 12:45 PM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:36 AM
    Thursday, April 10, 2014 9:13 PM
  • PowerPivot will automatically follow chains of 1 to many relationships, but you do currently need to use DAX when you introduce many to many bridge tables. Fortunately the pattern for many-to-many calculations is quite simple:

    [Customer Count] = CALCULATE( COUNTROWS( 'Customer' ) , 'CustomerAddress' )


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Friday, April 11, 2014 12:45 PM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:36 AM
    Thursday, April 10, 2014 3:53 AM
  • I've double checked too in a simple model similar to the setup that Murali has except modelling it as a many to many and using my calc expression and it works fine for me. Are you saying that you still getting repeating values in your pivot tables?

    Are your relationships setup to look like the following?

     Customer <---  CustomerAddress ---> Address

    I'm assuming that because you have a bridge one Customer can have multiple addresses or one address can have multiple customers.


    http://darren.gosbell.com - please mark correct answers


    In agreement with Darren...

    ...the modeling of the relationship should look like this...

    ..and you should be able to slice the customer measure/calculated field by any of the columns in the address table without getting repeated values, as illustrated here...

    ...The DAX formula for 'Number of Customers' follows the same many-to-many DAX pattern that Darren has mentioned in his earlier post...

    Number of Customers:=CALCULATE(COUNTROWS(Customer), 'CustomerAddress')

    Note: I created the above example is SSAS Tabular but the behaviour will be the same in Power Pivot ;)


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn


    • Edited by Michael Amadi Friday, April 11, 2014 1:04 PM
    • Proposed as answer by Murali dhar Monday, April 14, 2014 4:28 AM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:37 AM
    Friday, April 11, 2014 12:44 PM

All replies

  • PowerPivot will automatically follow chains of 1 to many relationships, but you do currently need to use DAX when you introduce many to many bridge tables. Fortunately the pattern for many-to-many calculations is quite simple:

    [Customer Count] = CALCULATE( COUNTROWS( 'Customer' ) , 'CustomerAddress' )


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Friday, April 11, 2014 12:45 PM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:36 AM
    Thursday, April 10, 2014 3:53 AM
  • I don't follow. That gives me the total count of customers who have an address, not a breakdown of customers per state.
    Thursday, April 10, 2014 2:07 PM
  • Hi Brittney, 

                     I simulated and I can able to get it. Where are you getting issue? in power pivot go to diagram view and create relation first.

    

    Then click pivottable into new sheet and drag address in row labels and customerid in sum values..

    • Edited by Murali dhar Thursday, April 10, 2014 5:55 PM
    • Proposed as answer by Murali dhar Friday, April 11, 2014 12:44 AM
    • Unproposed as answer by Darren GosbellMVP Friday, April 11, 2014 1:30 AM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:36 AM
    • Unmarked as answer by Elvis Long Tuesday, April 22, 2014 2:36 AM
    Thursday, April 10, 2014 5:52 PM
  • I've double checked too in a simple model similar to the setup that Murali has except modelling it as a many to many and using my calc expression and it works fine for me. Are you saying that you still getting repeating values in your pivot tables?

    Are your relationships setup to look like the following?

     Customer <---  CustomerAddress ---> Address

    I'm assuming that because you have a bridge one Customer can have multiple addresses or one address can have multiple customers.


    http://darren.gosbell.com - please mark correct answers


    • Edited by Darren GosbellMVP Friday, April 11, 2014 1:29 AM clarifying m2m scenario
    • Proposed as answer by Michael Amadi Friday, April 11, 2014 12:45 PM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:36 AM
    Thursday, April 10, 2014 9:13 PM
  • I've double checked too in a simple model similar to the setup that Murali has except modelling it as a many to many and using my calc expression and it works fine for me. Are you saying that you still getting repeating values in your pivot tables?

    Are your relationships setup to look like the following?

     Customer <---  CustomerAddress ---> Address

    I'm assuming that because you have a bridge one Customer can have multiple addresses or one address can have multiple customers.


    http://darren.gosbell.com - please mark correct answers


    In agreement with Darren...

    ...the modeling of the relationship should look like this...

    ..and you should be able to slice the customer measure/calculated field by any of the columns in the address table without getting repeated values, as illustrated here...

    ...The DAX formula for 'Number of Customers' follows the same many-to-many DAX pattern that Darren has mentioned in his earlier post...

    Number of Customers:=CALCULATE(COUNTROWS(Customer), 'CustomerAddress')

    Note: I created the above example is SSAS Tabular but the behaviour will be the same in Power Pivot ;)


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn


    • Edited by Michael Amadi Friday, April 11, 2014 1:04 PM
    • Proposed as answer by Murali dhar Monday, April 14, 2014 4:28 AM
    • Marked as answer by Elvis Long Tuesday, April 22, 2014 2:37 AM
    Friday, April 11, 2014 12:44 PM
  • Hi Darren/Michael,

                    Thanks for sharing valuable information. I know powerpivot but I'm not familiar with DAX. As you said if it is many to many relationship where we have to write this dax expression? Thanks in advance.

    Thanks.


    Friday, April 11, 2014 1:43 PM
  • ... where we have to write this dax expression? 

    You create a calculated measure in the grid area at the bottom of one of your tables.

    http://darren.gosbell.com - please mark correct answers

    Friday, April 11, 2014 2:07 PM
  • Thanks a lot sir.

    Friday, April 11, 2014 3:19 PM
  • Hi Murali, if any of the proposed answers above have solved your problem, please don't forget to mark them as an answer :)


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Sunday, April 13, 2014 6:56 PM
  • Hi Murali, if any of the proposed answers above have solved your problem, please don't forget to mark them as an answer :)


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn


    Well. I didn't started this post. I don't know whether it helped him/her.

    - please mark correct answers

    Monday, April 14, 2014 4:26 AM
  • Sorry Murali, the post was meant to address Brittney2013 :)

    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Monday, April 14, 2014 5:10 AM
  • np sir :)

    - please mark correct answers

    Monday, April 14, 2014 5:22 AM
  • Sorry Murali, the post was meant to address Brittney2013 :)

    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Hi Michael,

     Can you help me on this..http://social.msdn.microsoft.com/Forums/office/en-US/100e2c96-3c34-4c3b-be90-57cc713f0cd0/conditional-formatting?forum=exceldev


    - please mark correct answers

    Wednesday, April 23, 2014 5:25 PM