Forum Discussion
creating table/report from multiple tables
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.
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!
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).