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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    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]

amitchandak's avatar
amitchandak
Super User
3 years ago

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]