Combining data on a graph

[agraffeo21]

Hello,

I have a problem with graphing: I have two different queries with data sets -- one query has S&P 500 data and the date (in years) since 2002. So the data set has 2002, 2003, etc... twelve times for each month.

The other query has data for an index on consumer sentiment. It has the same dates -- from 2002 until 2015, twelve times for each month. 

Every time I try to plot the S&P 500 with the index, however, it causes one or the other index to remain constant for the entire graph. I have tried using both date data sets, but this problem still happens. 

The graph is at the very bottom of the screenshot. -- As you can see, the S&P data is fine, but the other consumer sentiment index is just a straight line.

Thank you in advance.

[ankitpatira]

@agraffeo21 Like kcantor mentioned you need to join Date column from S&P table with date column of 24 Country PCSI table under relationship view.

[agraffeo21]

I appreciate the responses! However when I go into my relationships, I cannot find my data for the consumer sentiment index. It should be headlined as "24 Country PCSI..." but it does not show up as shown in the screenshot...

I have tried to manually manage the relationships since the system cannot autodetect the dates, but when I connect the 24 Country PCSI and S&P dates, the data disappears.

Thank you for the help!

[Eric_Zhang]

@agraffeo21

The "24 Country PCSI"'s absence looks oddly. Can you upload your pbix? Or be more specific how to reproduce the problem.

By the way, what "24 Country PCSI" looks like in Data page?

[agraffeo21]

I cannot seem to upload the pbi to the forum...

I have the URL if you can see it from there? https://app.powerbi.com/groups/me/reports/acc1ce4e-5728-4b45-acfe-8f59ed12e2a2/ReportSection5

[Eric_Zhang]

@agraffeo21

---

@agraffeo21 wrote:

I cannot seem to upload the pbi to the forum...

I have the URL if you can see it from there? https://app.powerbi.com/groups/me/reports/acc1ce4e-5728-4b45-acfe-8f59ed12e2a2/ReportSection5

---

Can you try to upload the pbix to a web storage, like dropbox?

[agraffeo21]

I sure can. I uploaded it to Dropbox. Can I have your email to share it?

[Eric_Zhang]

@agraffeo21

You can post the dropbox link in a reply or you can send it via a private message.

[agraffeo21]

Great. https://www.dropbox.com/s/eyv7pl8v3v8677z/global%20PCSI.pbix?dl=0

[Eric_Zhang · Accepted solution]

@agraffeo21

The "24 country PCSI" is actually covered by "HPI&U MICH", if you move "HPI & U MICH" away it would appear in the relationship window. 😛

To achieve your requirement, I think you can create a calendar table and create relationship among tables properly.

DimDate = CALENDAR("2002-01-01","2016-12-31")

If this approach works, please accept it as solution. For any question, feel free to let me know.

[kcantor]

Do you have lookup tables related to your fact tables? If not, they will not share the axis as they are supposed to do. You need to use a field from the lookup table as the axis and the bars/lines come from your fact table.

http://www.powerpivotpro.com/2016/02/data-modeling-power-pivot-power-bi/