Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi All,

 

New to PowerBI and currently doing a small implemention for my business.

 

We have hooked up PowerBI with Salesforce and all works well but I'm trying to create a Line and Column Graph but can't get it to work.

 

Below is how my graph looks:

2020-01-13_144124.jpg

 

And we're trying to show:

Columns - Sales by Year (2019) & (2020) by Month (these are measures)

Line - Enquiries by Year (2019) & 2020) by month (these are also measures)

 

This issue I think I have is that the 'Sales' date comes from the Opportunity table and the 'Enquiries' data comes from the Lead table and both tables use a different date field (Lead>Date_Of_Enquiry__C) and (Opportunity>Purchase_Date__C).

 

I have read some articles and this forum around creating a calender and linking both fields to that but as you can see in the screenshot above, I have tried that and it still doesnt work.

 

Can anybody help me?

 

Thanks,


Ray

1 ACCEPTED SOLUTION

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

13 REPLIES 13
edhans
Super User
Super User

You have to create a date table, use the date table date in the chart, then relate both of your other tables to the date table. If you want to create a fully dynamic date table you never have to fool with again, see this article.

 

If you want to create a manual one, see the same article, but stop at the top when it shows you how to get started with hard-coded dates, then skip to the bottom on adding additional columns you may want to add, like month, year, week, day of week, etc.

 

Rule of thumb in Power BI is, if you are ever using dates, create a date table and turn off all of Power BI's automatic date logic it does in the settings, as you cannot control or even see what it is doing all of the time.

 

 



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
Anonymous
Not applicable

Hi Edhans,

 

I've followed a few Calender setup artices and below is what I have relationship wise:

2020-01-13_154831.jpg

 

As you can probably see in that screenshot, both relationships are set to inactive and I can either have one active or the other and I'm not sure how to get around that.

 

Does that make sense?

They should both be active. If they aren't it is likely because you have one or more bi-directional relationships set in the model that would cause ambiguity here. From a chart perspective, any inactive relationship may as well not exist. 

 

I'd like to see the entire model. It seems this is not a star schema model, which is essential to a successful Power BI report.

 

That said, you can activate inactive relationships within measures by using the USERELATIONSHIP(col1,col2,) function within a CALCULATE function as one of the filter arguments, but that won't help with a chart that is relying on the relationships to define the overall filter on the model.

 

If you can post your PBIX or more info, we can probably help. But I cannot do much by looking at a fraction of the model or understanding what all of the other tables are doing. 

 

The key here is a star schema. I've seen horrendous DAX code written to attemp to overcome a poorly designed model, one that looks great in an Access, SQL Server, or other relational database, but is not useful for a query model that Power BI depends on.



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
Anonymous
Not applicable

@edhans 

 

Thanks for your help and I agree that my model is probably no where near best or where it needs to be.

 

Please see below screen shots that show the model and also the relationships:

2020-01-14_093057.jpg2020-01-14_093243.jpg

 

Being very new to PBI, I'm pretty certain that there is something wrong with the relationships because it doesn't look right to me.

 

Thanks,

 

Raymond

Hi @Anonymous ,

 

The relationships are inactive between your fact tables and the calender table. Please use the function USERELATIONSHIP to work on it.

Please refer to this case.

https://community.powerbi.com/t5/Desktop/USERELATIONSHIP-issue/td-p/478744

 

If it doesn't meet your requirement,  Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Why are all of those relationships bi-directional? That should be used rarely, not always. That is why you cannot use the date table, the data model doesn't know how to make it work, so it is turning the relationships inactive.

 

That needs a lot of work and I'd love to sit down with you and walk through it. If you are like I was when starting with Power BI, you know a bit about relationships in databases, and the reality is I had to unlearn much of that because a relational database optimized for add/update/delete is not at all optimized for query, which is what Power BI is with its SSAS tabluar model.

 

What would happen if you disabled all of those bi-directional and just went with single direction, from the DIM (dimension, or master) table to the FACT (your facts or records) tables?

 

And you should use Power Query to collapse some of those tables into 1. For example, Opportunity looks like a FACT table. That is related to Contracts, which is related to Product, further related to Project.

 

You should use Contract as your main table, but merge the contents of Product and Project into it and just have the Contract table. Maybe Reservation too, but that might also be a FACT table. Cannot tell.

 

I know you are thinking "But holy cow, the redundency of data in that would be horrible." Not for Power BI. Having repetitive data in your DIM tables is no sweat. It makes writing measures easy and it causes the model to run fast. It breaks everything down into a column anyway, and compresses duplicate data. What you see as a 10 column table Power BI sees as a collection of 10 columns it is seeminly handling independently. What you have there is a snowflake schema (picture a snow flake where pieces just keep branching off of another branch). That is great for a relational database. But query models hate it. A star schema has a FACT table in the middle, and 1-6 (usually) DIM tables directly attached, and nothing beyond that first level of DIM tables. You can have multiple FACT tables and in essence multiple star schemas. For example, a sales table would be a FACT table, and so would a Purchase Order (to vendors) table, so you can have two fact tables in a model and each one as a star schema, even sharing some DIM tables, but each is a true star schema on its own.

I'd also make Contact a table, and merge Account into it. Not sure about Lead. Cannot tell if that is a FACT or DIM table. 

Does this make sense to you? I understand it is hard to grasp this in a quick post. 😊



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
Anonymous
Not applicable

@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

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
Anonymous
Not applicable

@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!

Excellent @Anonymous. 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
amitchandak
Super User
Super User

if you have already created as calendar and mark it as date, 1 Year behind measure should help you

1 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),datesadd('Date'[Date])
,-1,YEAR))

 

If not share your formula.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Anonymous
Not applicable

Hi AC,

 

Not sure what formulas you mean but my Measures looking like this:

 

Sales by Year 2020 = CALCULATE (COUNTROWS(Opportunity),YEAR (Opportunity[Purchaser_Signed_Date__c])= 2020)+0
Sales by Year 2019 = CALCULATE (COUNTROWS(Opportunity),YEAR (Opportunity[Purchaser_Signed_Date__c])= 2020)+0

 

There is some problem with the data model. Two dates are different tables, should have worked.

 

Sales by Year 2020 = CALCULATE (COUNTROWS(Opportunity),all(Opportunity[Purchaser_Signed_Date__c]),YEAR (Opportunity[Purchaser_Signed_Date__c])= 2020)+0

 Check if adding all make filter to work.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.