This topic contains 14 replies, has 2 voices, and was last updated by 
-
Posts
-
Attached is a demo file that demonstrates what I’m trying to do. The file contains two tables: one with a date range (called Items) and one is a time series table (called Sales). I added both tables to the Data Model and created a calendar table in Power Pivot to establish the relationships. I inserted a Pivot Chart that connects to the model. I’d like the plot of the Items data to be a series of dots beginning at Start Date and running through End Date. I could make a second time series table that refers to Items and does a simple check on whether the date in each row is valid for each of the Items, but I think that’s inefficient. I’m assuming you can do this with a measure, but I don’t know how to do it. Any ideas?
Please see attached Excel 2013 workbook for one approach to solution.
For this solution, no relationship between Calendar and Items is necessary.
Thanks! This is an interesting approach. I’m trying to avoid adding a column for each “Item” because there will be countless items. Do you know of a way to accomplish this task with a measure?
Attached is a workbook that uses a measure.
However, to write that measure I un-summarized you Items table by way of a “linkback” process that you can read about here and here.
I have run into this scenario a couple of times where date ranges are summarized in a table of from and to dates which essentially bypasses what Power Pivot was designed to do: aggregate and summarize. Instead of using a linkback table, you could import you “items” as a two column table which has one row for each Item and Date pair.
The calculation for the linkback table (which will refresh concurrently with the Items table) looks like this:
EVALUATE
SUMMARIZE (
FILTER (
CROSSJOIN ( Items, Calendar ),
Calendar[Date] >= Items[Start Date]
&& Calendar[Date] <= Items[End Date]
),
Items[Items],
Calendar[Date]
)
ORDER BY
[Items],
[Date]Thanks for pulling this together! I really appreciate your help. This is another interesting approach. I like that the table used by PowerPivot is dynamic.
Another possibility instead of a linkback table would be to have Power Query transform the source for the Items table into the DateItems table and load directly into the Power Pivot model. This would have the advantages of overcoming the 1048576 row limit imposed by an Excel worksheet, plus it would reduce the number of tables in the my last attached workbook’s data model.
That would be really great. Do you know what the query would look like to do that?
An interesting link to follow in this direction would be “Automatically Ranged Date Table Using DAX and M” by Kasper de Jonge:
http://www.powerpivotblog.nl/automatically-ranged-date-table-using-dax-and-m/
Thanks for your help! I ended up using a combination of the tricks (the DAX table from Kasper and power queries to dynamically join tables).
One thing I struggled with was plotting data from one table column as a separate series and comparing that to a different field. See image attached. As a workaround, I made a measure for each unique value. Normally, you’d use the Legend (series) area of a pivot chart for that, but adding another field duplicates that data for each value in the series. Is there any easy workaround for that? I suspect you could use a relationship, but I’m not sure how to do it.
Hi,
That does sound like some extra work, but hard (for me) to address the issue without a workbook with sample data.
Somewhat off topic for this issue, but more related to the DAX table from Kasper, in my copy of the code I have modified the line
NumberOfDates = Duration.Days(EndDate-StartDate),
to read:
NumberOfDates = Duration.Days(EndDate-StartDate) + 1,
When I initially created a calendar table with the original line of code and used StartDate of 1/1/2015 and an EndDate of 12/31/2015, the calendar table ended on 12/30/2015. Then I changed the Power Query code to add the 1.
Letting you know I have downloaded the file and, if you want to, you can remove it from your public drive.
Currently chewing on a couple of ideas.
Question: Regarding the Policy ID’s that are used in Policy City, Policy County, Policy State, and Policy Utility, is there a chance that they will conflict with each other?
Thanks! Regarding your question, no, IDs are only in one table: City, County, Utility, or State depending on their scope. Some policies are only for a particular city, others are for a county, etc. My idea is to use a pseudo-hierarchy allowing a user to look at a particular city and see all the policies that affect that city (including relevant county, utility, and state policies).
Nick,
I experimented with a copy of your workbook, and although it looked promising for a while, when everything was said and done, the only way to get the data onto the chart as needed was to create measures for the individual policy IDs.
My approach was to use Power Query to append Date and Policy ID columns from Policy City, Policy State, Policy County and Policy Utility. Once imported into Power Pivot, added a formatted column to display the IDs in a pattern like “ID 01” and created a relationship with the Calendar table. This worked great for getting the Policy IDs on the 2nd vertical axis, but without the individual policy ID measures, the chart never looked how I wanted it to.
I appreciated the chance to work with you on this project. If I get an insight in the next couple of days for another way to come at the chart without so many individual measures, I will definitely post here.
Best regards,
Tom
Thanks so much for thinking it through, Tom! I really appreciate your insights and your time. In the end, adding ~50 measures was a pain once, but subsequent additions take < 1 min so it shouldn’t be too much of a problem. My objectives were to keep maintenance as simple as possible and to reduce unnecessary data. I think my approach will work for my needs.
Nick
The forum ‘Power Pivot’ is closed to new topics and replies.