Reply
Member
Posts: 254
Registered: ‎06-15-2016

Creating relationships between tables

[ Edited ]

Evening,

 

I've created a means (a number of tables) in order to calculate the group sizes of customers from the transaction data based upon assumptions from the data table.

 

I'd like to be able to graph GroupSizebyID by Net Sales for example (in the attached pbix), but I can't work out how to link my group size table and my data table (itemdetailsdogfood$). I'm familiar with how to handle this given I've used summarize and gainstaged it a few times.

 

Thoughts on how to build the relationship?

 

Link to pbix: https://1drv.ms/u/s!At8Q-ZbRnAj8hkdBfK4ArZivdtCS

Super Contributor
Posts: 2,223
Registered: ‎07-17-2016

Re: Creating relationships between tables

Hi @ElliotP,

I'm familiar with how to handle this given I've used summarize and gainstaged it a few times.

After a few try, I was still not able to figure it out. However, I have studied a lot from your shared pbix.Smiley LOL

 

Hope someone else who is more familiar with DAX( @MattAllington@Sean@Vvelarde)can help figure it out.Smiley Happy

 

Regard

Super Contributor
Posts: 1,236
Registered: ‎07-03-2015

Re: Creating relationships between tables

I have taken a look at your workbook, but I have no idea what you are trying to do.  Can you please explain what you are trying to acheive while only refering to physical source tables - eg not refering to any calculated tables.  To help you I need to understand what you are trying to acheive.  Even an excel spreadsheet with the raw data that simulates what you want would be useful.

Member
Posts: 167
Registered: ‎05-02-2016

Re: Creating relationships between tables

@ElliotP: Try to move all you calculated tables created using DAX to Power Query/Query editor level and make appropriate relationships in a relationship view. Linking DAX calculated tables to normal tables context is not easy

Member
Posts: 254
Registered: ‎06-15-2016

Re: Creating relationships between tables

@MattAllington

 

There are five tables in total. I've done them in this manner to catch some conditions.

 

The first table is; ItemDetailsGroupBySection2:

ItemDetailsGroupBySection2 = SUMMARIZE('itemdetailsdogfood$','itemdetailsdogfood$'[Date],'itemdetailsdogfood$'[Time],'itemdetailsdogfood$'[Notes],'itemdetailsdogfood$'[Transaction ID],'itemdetailsdogfood$'[Sections2],"GroupSize",SUM('itemdetailsdogfood$'[People]),"Net Sales",SUM('itemdetailsdogfood$'[Net Sales]))

This essentially is refining th data table down to a more managable size. The 'itemdetailsdogfood$' [people] column is simply people = 1; as the transaction data is per item. So there can be a number of items per order (number of rows), but they all share the same unique transaction ID.

 

The second table is; ItemDetailsSlicebyNotes:

ItemDetailsSliceByNotes = FILTER(ItemDetailsGroupBySection2,ItemDetailsGroupBySection2[Notes] <> "")

This table enables me to handle the first condition. The first condition is if multiple orders (different unique transaction IDs) have the same notes value from the 'itemdetailsdogfood$' data table such as "25in" within 10minutes, then they need to be counted as being apart of a group.

 

The third table is; ItemDetailsGroupByNotes:

ItemDetailsGroupByNotes = SUMMARIZE(ItemDetailsSliceByNotes,ItemDetailsSliceByNotes[Date],ItemDetailsSliceByNotes[Notes],ItemDetailsSliceByNotes[Adjusted Time for Notes],'ItemDetailsSliceByNotes'[Transaction ID],"GroupSizeNotes",SUM(ItemDetailsSliceByNotes[GroupSize]),"Net Sales",SUM(ItemDetailsSliceByNotes[Net Sales]))

This table brings it all together a little bit more.

 

The fourth table is: ItemDetailsGroupByTransactionID:

ItemDetailsGroupByTransactionID = SUMMARIZE(FILTER(ItemDetailsGroupBySection2,ItemDetailsGroupBySection2[Notes] = ""),ItemDetailsGroupBySection2[Date],ItemDetailsGroupBySection2[Transaction ID],"GroupSizeID",MAX(ItemDetailsGroupBySection2[GroupSize]),"Net Sales",SUM(ItemDetailsGroupBySection2[Net Sales]))

This is to allow for the second condition; if there are more than a certain number of products per order then it changes the group size. So for example; if on one order, one unique transaction ID, there are 2 meals, 1 coffee, 1 juice and 1 piece of cake, then this is a group of two people due to the two main meals. This operates off the principle that people only order one main meal per person, people only order one coffee per person. As such, I'm able to discern group sizes and shapes even when they order together (this one was the toughest to work out).

 

The fifth table is; Group Size Cross Join:

Group Size Cross Join = CROSSJOIN(CALCULATETABLE(SUMMARIZE(ItemDetailsGroupByTransactionID,ItemDetailsGroupByTransactionID[GroupSizeID],"Group Size by ID",COUNT(ItemDetailsGroupByTransactionID[GroupSizeID]),"Net Sales by ID",SUM(ItemDetailsGroupByTransactionID[Net Sales]))),CALCULATETABLE(SUMMARIZE(ItemDetailsGroupByNotes,ItemDetailsGroupByNotes[GroupSizeNotes],"Group Size by Notes",COUNT(ItemDetailsGroupByNotes[GroupSizeNotes]),"Net Sales by Notes",SUM(ItemDetailsGroupByNotes[Net Sales]))))

This brings it all together again, as well as the NetSales table so I'm able to show the group sizes and their net sales. This is the point as well where I would like to be able to somehow bring/relate the Date and Time as so I can graph Group Sizes, The number of the group sizes, etc over a date time period.

 

The sixth table is; Group Size

Group Size = SUMMARIZE(FILTER('Group Size Cross Join','Group Size Cross Join'[GroupSizeID]='Group Size Cross Join'[GroupSizeNotes]),'Group Size Cross Join'[GroupSizeID],'Group Size Cross Join'[Group Count],'Group Size Cross Join'[Net Sales])

Just brings it together.

 

I'd like to be able to calculate my Group Sizes, the number of group sizes, the net sales of the group sizes, with a date&time relation so I can show it it develops and evolves.

 

@SunkariThis is a suggestion I like the most. I'm honestly not sure how to (might put time into it soon as it seems like the easiest and best way). I could probably refine these tables and make the much more aggreeable and workable (2-3 tables instead of 6) as well.

 

 

 

Super Contributor
Posts: 1,236
Registered: ‎07-03-2015

Re: Creating relationships between tables

 None of these tables are your raw data tables, they are all calculated tables. What I would like to understand is "what" you are trying to achieve given the raw data you have. You have provided me "how" you are trying to achieve it, and that is something completely different (and apparently not giving you what you need). 

 

So can you please explain the raw data tables you have and what you want to achieve. 

Member
Posts: 254
Registered: ‎06-15-2016

Re: Creating relationships between tables

I would like to from my original data table 'itemdetailsdogfood$' calculate the size of groups and number of groups as a result of the two conditions above.
Super Contributor
Posts: 1,236
Registered: ‎07-03-2015

Re: Creating relationships between tables

I don't think this is a job for Power Pivot (I could be wrong).  As I understand, you want to create some new data from the data you have - the new data being some sort of unique identifier that analyses the transactions and determines which transactions should be considered to be part of the same "group" .  I guess that some single transactions are also = 1 group.  Is that correct?  If so, I would use Power Query to idenfity the single transactions that are also a group first and put them into a staging table in Power Query (don't load it, just old it as a query that is not loaded).  Make sure you included any other relevant information you want for later analysis.  Then I would create a second (and maybe more) queries to apply your rules over the remaining records at are not 1 transaction = 1 group and assign them a Group ID - something unique that can be used later to idenfity them as part of the same group.  Maybe you have to do the groups first and the single transactions later - not sure.

 

Sorry, I don't know if that helps or not

 

 

Highlighted
Member
Posts: 254
Registered: ‎06-15-2016

Re: Creating relationships between tables

It does help. I'm not looking to create unique identifiers for the data, but I am looking to pull from the data and classify them based upon two conditions.

 

I like your suggestion of grouping the data and doing it that way.