Forum Discussion

ekinmur's avatar
ekinmur
Regular Visitor
10 years ago

M:1 relationship not observed

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), VAL...
Need Help
ekinmur's avatar
ekinmur
Regular Visitor
10 years ago

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's avatar
wonga
Icon for Continued Contributor rankContinued Contributor
10 years ago

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's avatar
    ekinmur
    Regular Visitor
    10 years ago

    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's avatar
      wonga
      Icon for Continued Contributor rankContinued Contributor
      10 years ago

      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's avatar
        ekinmur
        Regular Visitor
        10 years ago

        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.