Forum Discussion
Show measures by different dates on same timeline
Hello,
I have a Leads table, which links to an Applications table, many to one. Leads has date, name, email, etc. Applications has Date applied, name, email etc. I am trying to figure out how to display a column chart by month, total Leads and total Applications. But, I want to show total Leads by the month of the lead date and total Applications by the month of the Applied date. For example, in January we had 50 leads (so display 50 on January), and out of these, 10 applied in Feb, 40 in March so those Applications need to be under Feb and March. Since these 2 tables have a relationship, I can't figure out how to display them by different dates?? Right now I just have Lead Month and Year on the X axis, Total Leads and Total Applications on the Y, so it is displaying the Applications by the month the lead for that applicaiton came in, not the actual month they applied.
3 Replies
Hi aashton
You need a Date Table that has relationship on the Date column of both Applications and Leads table on their date column. Remove or deactivate the current relationship between them both.
https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables
Create a the Bar chart. Ad´d the Year and month columns from the date table in the axis and then add the value columns from both tables in the value field. this will show you then what you need.
Thanks
Joe
If this post helps, then please Accept it as the solution
JoeBarry Thank you so much for your help. I thought I had to do something like this, but i need the relationship between Leads and Applications, joined on email, to only see the applications for those people. I only want to show applications for the Lead entries (there are many more applications for people not in the Leads table), so how would I accomplish this?
Create a table with all of the distinct email addresses and create a one to many relationships between both tables.
read up on the star schema, factvtables shouldn't have relationships with each other.
