M:1 relationship not observed

ekinmur · ‎09-06-2016

Hi,

I have two tables T1 and T2; T1 has an id column that has unique values, T2 has a calculated column that I created as follows:

Column = IF(VALUE(T2[sprint_id]) = BLANK(), VALUE(1595), VALUE(T2[sprint_id]))

I am specifying M:1 relationship between T1 and T2 using the columns id and Column above.

There is a description column (desc) in T1 that I would like to pull into a report with Column (and some other columns from T2). When I select the desc column from T1 in the fields, it repeats all the T1 values for each Column entry in T2 (so if T2's cardinality is X and T1's Y, I end up with a table of cardinality X*Y)

So, it looks like Power BI desktop is cross joining T1 and T2. Why does it not respect the M:1 relationship?

thanks,

Murat

v-ljerr-msft · ‎09-08-2016

@ekinmur

According to your description, I have made a sample and tested it on my side. It works all fine for me. Power BI Desktop does not cross join T1 and T2, and respect the M : 1 relationship well. I cannot figure out why it doesn't work for you. Could you post some sample data in your case? It's better to upload your pbix file.

The sample below is for your reference.

T1.

T2.

Relationship between T1 and T2.

Result in report.

Regards

ekinmur · ‎09-08-2016

@v-ljerr-msft

Thanks for your reply. Your sample indeed describes my scenario, so I am a bit baffled. After adding yet another column to T2, power BI desktop runs out of memory to post the result, which further leads me to think that the M:1 relationship is not recognized somehow in my case. I also do not see the 'Cross filter direction' option/drop-down enabled, meaning I cannot select 'Both' as an option. I don't know if I can post my pbix file as it might be too much of an effort to scramble the data on my part. I'll see what I can do..

In the meantime, would you have any pointers based on the 'cross filter direction' being disabled? does it indeed indicate T1[id] and T2[Column] are not quite right?

thanks again

wonga · ‎09-08-2016

@ekinmur

Usually when the cross filter direction is disabled or it can't be changed, it's usually because of another relationship that exists between other tables. Try setting the cross filter direction to "single" for some of the other relationships if you have them and see if cross filter for this particular relationship can be changed.

ekinmur · ‎09-08-2016

@wonga

does power BI desktop auto assign relationships if the two tables have common column names?

In 'Edit relationship' I can only see those two columns highlighted (and'Manage Relationships' modal only shows these two columns in parantheses), but T2 has another column called 'id'. It is not specified in the relationship though.

wonga · ‎09-08-2016

@ekinmur Yes, by default PowerBI auto detects relationships between tables. Sometimes this auto-detection may result in an incorrect relationship, so it's always best practice to double check it just to make sure. 9 times out of 10 though, the auto detection is correct.

Are the auto detected related columns between the two tables correct or not? If not, you can modify the relationship to what you think it right and Power BI will let you know if it's valid.

ekinmur · ‎09-08-2016

@wongaso T1 has an id column and T2 also has an id column, but the relationship between the two is not correct. There is a relationship between the calculated column Column in T2 and id in T1, which I specified and is the only relationship showing in the relationship manager modal.

If power BI is implicitly defining a relationship between T1[id] and T2[id], it is not showing in the editor and I thus don't know where to disable it.

wonga · ‎09-08-2016

@ekinmur Okay, so it looks like there were no relationships that were automatically made upon loading data into PBI. If the only relationship you see in the relationship manager is the one you created, then it shouldn't be a problem. You should be able to choose the "Both" option for cross filter direction since there are no other relationships that could cause conflicts.

ekinmur · ‎09-08-2016

@wonga is there a known bug?

i guess i am getting to the point where i have to upload my pbix file...

wonga · ‎09-08-2016

@ekinmurYou could do that, but I'm not sure how much help it would be since it doesn't look like there's anything wrong functionally.

ekinmur · ‎09-09-2016

@wonga is there another path/forum to report potential bugs for power BI relationship management then? I would not say the functionality is correct, at least for my use case.