Power Pivot - Table Relationship Question

[devlynsyde]

I'm having difficulty getting what I want to do from PowerPivot due to lack of understanding it completely still. I have the 2010 version and am trying to relate a detail table of the past to a summary table for the current month.


My current linked identifier is the customer number & item code. In the summary table this is an unique identifier. In the detail report it would not be. I want to be able to pull the last sale of the customer over all, the last sale of the customer / item combination, and the total sales of that time period. This is working fine as long as the customer purchased the item in both the current month and the previous months.



But.. if they didn’t purchase any of the items from this month, in previous months, it won’t return any date. How can I create a proper relationship between tables of this type?

 

[devlynsyde]

I tried creating a helper table with unique customers from the current month, linking it to the previous months, but I cant display the non-purchasers in my pivot. And I can't drop the items in.

 

[scottsen]

It's a bit hard to tell from your description what is going on... but to me, it sounds like you would benefit from a typical "calendar table" (or "date table", whatever you like to call it).

 

[devlynsyde]

=calculate(max(stats[posting date]),filter(stats,stats[customer id]=maystats[customer id]))
=calculate(max(stats[posting date]),filter(stats,stats[customer id]=maystats[customer id]),filter(stats,stats[item no#]=MayStats[Item No#]))

I was able to work out the syntax is a calculated column, but it doesn’t seem to work if I use the same DAX as a measure in the pivot table.I didn’t want to add add’l columns to the table for the calculation.. and I don’t understand why it fails as a measure with the same syntax.



The thing with the date table that I can’t figure out, is if I have multiple tables that I want to relate to it, all of the relationships can’t be active.So for example in this particular report I have a 6 month history table and a current month.Right now I can’t put them together because one is a detail and one is a summary.But even if I had 7 months of detail, is it possible to compare in that way?What I need to know is if a customer purchased in the last month, had they purchased in the previous 6.

 

[GDRIII]

You should be able to relate as many tables to your date table as you want. If you are seeing an inactive relationship between one of your tables and the dates table you might have a formatting issue on one of your date columns. You can't have two date relationships within a single table...

I've had issue with time stamps being recorded in my data versus my date table all being midnight of that day...a calculated column using DATEVALUE will fix that right up.

Once you figure out the summary versus detail issue, it sounds like an IF for your current period with a DATESBETWEEN for your history might be the way to go.

 

[devlynsyde]

Ahh, yes you are correct, it is based on having two dates I want to relate to the date table. I have order created, order shipped, and invoice posted all as dates within the output table that I am working on. I want to relate them back to the date table in order to give them Months, Quarters, etc. What is the standard way of dealing with this? Multiple Date Tables so you can do this? It'd get awfully redundant..

 

[GDRIII]

Since you indicated you want to compare who has ordered this month versus who ordered in the last 6 months, I would go with Date Created...assuming that not all orders are posted and each order gets a Date Created date at order entry.

I really am not sure what "them" in order to give Months, Quarters, Etc.

Maybe a litte more clarity but, you should be able to write specific measures for Open vs Posted etc using CALCULATE, and then compare with IF/DATESBETWEEN patterns...

Maybe scottsen will have a better idea once he gets enough coffee and gets on here.

 

[scottsen]

Ok, first cup of coffee finished

A few things:

1. Having multiple tables all related to the date table is normal, common, and won't cause any inactive relationships
2. As you have found, you can not have multiple relationships between the same tables. In that case, you will get inactive relationships... you should probably pick your "favorite" relationship and leave it active.
3. To "activate" the relationships... you use the USERELATIONSHIP() function. See USERELATIONSHIP Function (DAX)
4. When doing a calculated column (as opposed to calculated field / measure), it is evaluated 1 row at a time without a filter context. When you hit a CALCULATE, it creates a filter context against the whole table.
5. That, of course, is not the case when you migrate your calc column to a measure. It's going to be evaluated in the current context (at least the coordinates in your pivot table), and won't be against the WHOLE table. You might try simple changing your filter(stats... to filter(ALL(stats)... and see if that gives you what you want?

 

[devlynsyde]

Ok, first cup of coffee finished

A few things:

1. Having multiple tables all related to the date table is normal, common, and won't cause any inactive relationships
2. As you have found, you can not have multiple relationships between the same tables. In that case, you will get inactive relationships... you should probably pick your "favorite" relationship and leave it active.
3. To "activate" the relationships... you use the USERELATIONSHIP() function. See USERELATIONSHIP Function (DAX)
4. When doing a calculated column (as opposed to calculated field / measure), it is evaluated 1 row at a time without a filter context. When you hit a CALCULATE, it creates a filter context against the whole table.
5. That, of course, is not the case when you migrate your calc column to a measure. It's going to be evaluated in the current context (at least the coordinates in your pivot table), and won't be against the WHOLE table. You might try simple changing your filter(stats... to filter(ALL(stats)... and see if that gives you what you want?

Thanks so much for all of the help from all. It’s been awkward working in “Excel” and not knowing the syntax or even the possibilities with DAX. I will read up on USERELATIONSHIP more, as that certainly is what I was needing.

Point 5 is still confusing to me, I’ll try to read more on the differences in how the column calculation is processed vs a measure.

What I’m attempting to do seems simple but has proven difficult. I essentially want to be able to provide a count of customers that purchased products in May, that had not purchased in the previous month, by salesperson. I had it completed, but was using one table for 6 months and another table for the month in question. I’m trying to provide more utility, so that the month in review can be altered, without having to reload the tables with difference data. Hopefully between DATEBETWEEN and USERELATIONSHIP I can figure it out, once I’ve got a better grasp on how to create measures instead of using Calculated Columns.