cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Plexus
Frequent Visitor

Re: Line and Column Chart - Populate with 2 different sets of Dates from 2 different Tables

@edhans 

 

Thanks for your help help and guidance with this.

I took your feedback and turned the bi-directional relationships off (Not sure why they were setup like that) and it seems to have fixed it and the dates are now working as they should! 

 

As for the second part of your post, I've read a few things about powerquery and putting tables together but I don't quite understand how it works. If I remove the Project, Product and Reservation tables and merge them with the Contracts table, what happens when I reresh the data from Salesforce? Will the data go back into the merged table?

 

Is there anywhere that explains this is laymans terms?

 

Thanks again,


Ray

Super User II
Super User II

Re: Line and Column Chart - Populate with 2 different sets of Dates from 2 different Tables

Glad the crossfilter direction thing helped.

 

As to the tables, they will all work. For example, I suggested keeping the Contracts table and merging the required fields from Product then Project. Assume that is a correct assumption on my part. (HUGE assumption. 😁 )

You could do all necessary transformations to Product and Project first, then starting with Product, merge the Product table and expand the relevant columns.

 

Then clean up Contracts as needed, then merge the Product table (which is now Product + Project) and expand the necessary columns.

 

Don't load Project and Product, but do load Contracts. When you refresh. all 3 tables will be refreshed and the loaded table Contracts will have all of the relevant info. It might look something like this (I did this in Excel, so it might look slightly different):

20200115 16_47_39-Contract - Power Query Editor.png I keep my queries in Power Query segregated by folders to make it easy when I have a model that has 15 source tables, 12 intermediate queries that do varous transformations, and 8 tables that ultiumately load. But know that if a table loads, Power Query works backward to ensure up the chain that every table/query it depends on is also refreshed. PQ actually refreshes bottom up, not top down. So if you connect to a table from another source but never merge/append or even load it, it will not be refreshed, as nothing loaded is using it.

Make sense?

As for resources, you may find some blogs that can help, but honestly, this book will be the best IMHO to help you out. Every book the SQLBI team puts out is worth reading. I'm working my way through their Definitive Guide to DAX Second Edition released last year.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Plexus
Frequent Visitor

Re: Line and Column Chart - Populate with 2 different sets of Dates from 2 different Tables

@edhans 

Thanks for all of your help with this, you've been able to bring clarity to what was a lot of complications! 

 

I've been able to clean up our data model based on your feedback and that has made things easier.

 

Thanks again!

Super User II
Super User II

Re: Line and Column Chart - Populate with 2 different sets of Dates from 2 different Tables

Excellent @Plexus. Spending a few hours in Power Query creating a nice clean model can save days of frustration on the DAX side. Glad my proposal helped!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors