caller_id with caller details

[Anonymous]

Hi All,

Forgive me if this question is already answered but, I am facing issues with my data model. I have a fact table with a column caller_i and contains the actual name of the corresponding caller and it is type text. there is a dimension that has all the details of the respective caller.

I want to link this dimension to the caller id but not successful even I tried creating a surrogate key in my caller dimension and linking it back to fact. I assume this is because names can be the same. Below is the screenshot of my user table. I am trying to link this dimension using the Userkey to the fact. You can see I have the same names in the dimension.

[v-alq-msft]

Hi, @Anonymous 

I wonder if your data model is a star schema and what the cross filtering directions between tables are like. Are there any error messages when you link this dimension to the caller id?

Power BI Desktop looks at column names in the tables you're querying to determine if there are any potential relationships. If there are, those relationships are created automatically. If Power BI Desktop can't determine with a high level of confidence there's a match, it doesn't create the relationship. However, you can still use the Manage relationships dialog box to manually create or edit relationships. For further information, please refer Create and manage relationships in Power BI Desktop .

Cross filtering both directions works well with a star schema. If you have a table pattern like below, with loops, cross filtering can create an ambiguous set of relationships. For example, if you sum up a field from TableX and then choose to filter by a field on TableY, then it’s not clear how the filter should travel, through the top table or the bottom table.

Best Regards

Allan

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

[Anonymous · Accepted solution]

Hi @v-alq-msft I was able to resolve this using surrogate key in fact and dimension table. 

[amitchandak]

@Anonymous , You can create a combined column and use it but looking at this data I am not sure you can do that.

Can you share sample data and sample output in table format?

New Key = [Employee Name] & "-" & [Col2]

You need to this kind of column on both side to have a single key

[Anonymous]

hi @amitchandak. Below is the sample data the name column in the table is caller id in the fact table.

name	Entity	sys_created_on	sys_updated_on	u_cap_bu	u_cap_sbu	u_employee_type	UserKey
Usha Usha	CIS	########	########	India MS	Cloud Infrastructure Services (CIS)	Employee	242846
? O365	Unknown	########	########			Employee	1
A Anshuman Patra	DEMS	########	########	DEMS India OBU	Engineering Services	Employee	2
A Ashok	Europe	########	########	Apps India OBU SAP AM	Europe SBU	Employee	3
a baba sai venkata ganesh	DEMS	########	########	DEMS India OBU	Engineering Services	Employee	239934
A Balakrishna	Americas & APAC	########	########	APAC Platform	Americas & APAC SBU	Employee	4
A Bhanu Chander	Americas & APAC	########	########	LP AS NA AandA	Americas & APAC SBU	Employee	5
A Bharath Teja Reddy	FS	########	########	FS SBU Global Costs	Global FS SBU	Employee	6
A Bhargava Reddy	I&D	########	########	I and D India ACIS	Insights & Data	Employee	7

[amitchandak]

@Anonymous , User Key should be unique on both side

else new column in both tables

Key  = [User Key]  & "-" & [Enity]

or

Key  = [User Key]  & "-" & [Enity] & "-" & [Name]

See if this can work out

[Anonymous]

hi, @amitchandak UseKey is the value I created it's not coming from the database. My fact table only has caller-is which is the user name .

All other information like entity and others are in the user table not in fact.