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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.