Forum Discussion

Composed_MS's avatar
Composed_MS
Helper I
3 years ago
Solved

Confusion in Connection between tables

I have three tables -> 

1 Data_country

2 Customers

3 Ratings

 

 

Now I have two cases-> 

Case 1->

If I do not put any connection between them and then in one table visual I put country column from Data_country table and customer column from Customers table, then it gives me error "Couldn't find relationship between tables", I don't have any problem with this as I understand that Power BI can't find the relationship between them.

 

 

Case 2-> If I create two connections 
1) Data_country table and Ratings table 
2) Customer table and Ratings table
As shown below and then do the same thing of putting Country from Data_country table and Customer from Customers table in Table visual, I get the values.

 

 

 

I am not able to undertand that how I am getting the values as my connection is "Single Cross filtered" between Country table and Ratings as well as Customer table and Ratings, so my Data_Country table should not be filtered and similarly Customer table should not be filtered then how is it getting the connection between Data_Country table and Customer table. I think it should still not find any connection.

Please help in removing this confusion.

 

 

 

 

  • Composed_MS , Power bi does, That. I have taken this query from the performance analyzer, for similar two tables - only joined with fact. Check query power bi is running

     

    Common fact count is added

    // DAX Query
    DEFINE
    VAR __DS0Core =
    SELECTCOLUMNS(
    KEEPFILTERS(
    FILTER(
    KEEPFILTERS(
    SUMMARIZECOLUMNS('Item'[Brand], 'Geography'[State], "CountRowsSales", CALCULATE(COUNTROWS('Sales')))
    ),
    OR(
    NOT(ISBLANK('Item'[Brand])),
    NOT(ISBLANK('Geography'[State]))
    )
    )
    ),
    "'Item'[Brand]", 'Item'[Brand],
    "'Geography'[State]", 'Geography'[State]
    )

    VAR __DS0PrimaryWindowed =
    TOPN(501, __DS0Core, 'Item'[Brand], 1, 'Geography'[State], 1)

    EVALUATE
    __DS0PrimaryWindowed

    ORDER BY
    'Item'[Brand], 'Geography'[State]

1 Reply

  • Composed_MS , Power bi does, That. I have taken this query from the performance analyzer, for similar two tables - only joined with fact. Check query power bi is running

     

    Common fact count is added

    // DAX Query
    DEFINE
    VAR __DS0Core =
    SELECTCOLUMNS(
    KEEPFILTERS(
    FILTER(
    KEEPFILTERS(
    SUMMARIZECOLUMNS('Item'[Brand], 'Geography'[State], "CountRowsSales", CALCULATE(COUNTROWS('Sales')))
    ),
    OR(
    NOT(ISBLANK('Item'[Brand])),
    NOT(ISBLANK('Geography'[State]))
    )
    )
    ),
    "'Item'[Brand]", 'Item'[Brand],
    "'Geography'[State]", 'Geography'[State]
    )

    VAR __DS0PrimaryWindowed =
    TOPN(501, __DS0Core, 'Item'[Brand], 1, 'Geography'[State], 1)

    EVALUATE
    __DS0PrimaryWindowed

    ORDER BY
    'Item'[Brand], 'Geography'[State]