Solved: Line and Column Chart - Populate with 2 different ... - Page 2

Anonymous · ‎01-12-2020

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:

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

edhans · ‎01-15-2020

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):

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.