creating table/report from multiple tables

[Anonymous]

Hi,

I am new to power bi and trying to create table/report that pulls data from three different tables.

I have one table "Conversion":  with columns like: conversion_id, date_converted,channel, revenue and other columns and measures 

Second table "Qualification": Qualification_id, date_qualified, channel, revenue and other columns and measures

Third table "Spend" : Date, channel, Spend and other columns and measures

Qualification_id is same as  conversion_id but it could have different dates  (dates for id that got converted and qualified many times differ) and not every id that converts qualifies. 

I want to display sum(spend) , count of qualifications , count of conversion and qualification rate (qualification/conversion) grouped by date and channel in one report

Date	Channel	sum(Spend)	count of conversion_id	count of qualification_id	Qualification Rate (count of qualification id)/(count of conversion id )
1-May-18	Paid	$4,000	27000	9000	33%
1-May-18	Organic	$0	78000	6000	8%

There were no relationships detected in amongst these three tables. Please note that the date values are not unique. I am looking into creating star schema and calculated table but since I am super new to this I am struggling to create this view. Any help would be appreciated!

Also, I tried creating a date lookup table- but when I create relation with qualification and conversion table , the data in dashboard disappears:

As soon as I delete one of the relationship data appears

Not sure what I am doing wrong here. Thank you!

[Seward12533 · Accepted solution]

You need a bridge table of all valid IDs that could appear in both.  (If one is not a superset of the other you can build it in PowerQuery to duplicate each table, remove other columns, then remove duplicates and then create an append table of these two and then remove duplicates again.)

You will probalby have to remove your bidirectional "Both" filter directions on your relationships with the date table in order to define the relationships. 

Write you measure for Count, Sum or whatever from each of the tables, once you have done that you will need to force the filter context.  There are several ways to do this. Here is a copy of something I did for an internal workshop. 

[Anonymous]

@Seward12533

Thank you for your help! I created a bridge table with unique ids and created  1 on 1 relation cross filter direction: both (tried sigle too)   with Qulaification and conversion but that is still giving me incorrect data. Count of conversion id and qualification_id cannot be same for a particual time period

channel	Count of qualification_id	Count of conversion_id
#1 - Direct	40	40
#3 - Organic	144	144
#7 - 800 Call-Ins	3	3
#7.5 - 800 Call-Ins (Home Page)	3	3
#8 - Untagged/Other	3	3
Branded Paid Search	1	1
Paid Search	70	70
Paid Social	14	14
Partners	41	41
Referral	1	1

when I created a date bridge table and created a relation with Qulaification and Conversion table , I get this output: 

channel	Count of qualification_id	Count of conversion_id
#1 - Direct	40	1097
#3 - Organic	144	1097
#7 - 800 Call-Ins	3	1097
#7.5 - 800 Call-Ins (Home Page)	3	1097
#8 - Untagged/Other	3	1097
Branded Paid Search	1	1097
Paid Search	70	1097
Paid Social	14	1097
Partners	41	1097
Referral	1	1097

I have another follow up question: In my case I need the data to be group by multiple dimension- like date, channel etc do I need to create bridge table for each?

Thank You!

[Seward12533]

You need a bridge table for each.   If you have a Bridge or Lookup Table for Channel are you using that in your table if so would work if you used the Channel from your bridge/lookup  table it will force the filter contect "Down" (direction fo the arrows) into each of the tables and it shoudl work. 

If you do have a relationship for channel defined between the two table, the reason your 2nd example is returing the Total Count is that its not applying the filter context from the channel name which I'm assuming your getting from the table with qualification IDs.   You need to wrap your measure for the Count of Conversion ID with a CALCULATE and specify the other table name as the fitler term. (see example I posted).