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
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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.:smileyhappy:

 

The sample below is for your reference.

 

T1.

T2.

Relationship between T1 and T2.

Result in report.

 

Regards

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.