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), 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

Need Help

11 Replies

  • 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.