Outcome mismatch when relating data across two tables

[CAVCX]

Hello,

I have recently started to use PowerBI for analysing my startup business data. I am able to do basic analysis when it comes to a single data source, but am facing some issues when I try to connect across tables.

DATA / RELATIONS

I have a table ACCOUNT_SUMMARY which I export from my website database, where I have a single row item for each account in my business. The unique key there is ACCOUNT ID. This table has various date points when the account has done some key activity, e.g. registered, made a project, done first usage, done first payment etc. Each of these is a column of data across each record.

I have another table ACCS 2021 which is exported from my CRM, which also has the same unique key but its called ACCOUNT NUMBER in this table. Each account number has only 1 row in the table and the data columns include creation time, campaign, ad id etc.

I have created the relationship between the two tables and the relevant fields.

PROBLEM

I want to make a single table which shows the following for a given time frame, which I can put with a slicer

Campaign Name | # of Registered Accounts (Count of Created Time) | # of Converted Accounts (Count of conversion date)

I tried to make the table but the output is as follows

Ad Campaign Name	Count of Created Time	Count of Conversion Date
	257	260
CPaaS-Competitor | India | May-09-19 | Conversions	4	260
CPaaS-SMS | India | Aug-05-20 | Conversion	71	260
CPaaS-Video | India | May-09-19 | Conversions	62	260
CPaaS-Video | India | May-09-19 | WebRTC | Conversions #2	1	260
CPaaS-Voice | India | Sep-28-20 | Conversion	31	260

Since column 2 and 1 are from the same table, i'm getting the count correctly. But the 3rd column is being pulled from the other table, and is just doing a full count of all cells, rather than just those which have a date entry in them.

Any suggestions?

Thanks

[CAVCX]

Hello,

I couldn't figure out what "filter direction" meant.

But While both tables only a single row item for the unique field A/C ID, when I changed the relation to Many-to-Many, the numbers got updated properly.

Thanks for the lead.

[lbendlin]

Filter direction means how the arrows are pointing on the link between the tables. 

Only use many-to-many if absolutely necessary. Ideally you should only use 1-to-many from the dimension table to the fact table.

[CAVCX]

Hello,

Please see my relationships

The tables Account_Summary_R and Accs 2021 both have the same unique identifier called Account ID in the first table and Account Number in the second table. Both will only have 1 row item for each unique field. However, it is still not connecting unless I do a many-to-many.

Any thoughts?

Regards

[lbendlin · Accepted solution]

Check the filter direction in your data model. Seeing the same number over and over means you cannot "get there from here"  based on the filter arrows.

[v-stephen-msft]

Hi @CAVCX ,

Does @lbendlin 's reply help you? If you're still confused, please provide dummy data and expected results.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.