Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Composed_MS
Helper I
Helper I

Confusion in Connection between tables

I have three tables -> 

1 Data_country

2 Customers

3 Ratings

 

Composed_MS_0-1679841316690.png

 

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.

 

Composed_MS_1-1679841450961.png

Composed_MS_2-1679841540639.png

 

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.

Composed_MS_3-1679841940342.png

 

Composed_MS_5-1679841985079.png

 

 

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.

 

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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]

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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]

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.