Forum Discussion
My DirectQuery dataset model appears to be using Inner Joins as default
- 5 years ago
Susan_J it pulls 1 million rows see limitation section Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
It's me again - the original poster. I'm wondering whether the issue has something to do with the fact that my Fact table has over a million rows and DirectQuery isn't mad keen on table with over a million rows - could this be affecting the query generated?
If I run Performance analyzer over my analysis then I get this DAX: -
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"R45T"}, 'EMEA LISC'[Item_Number])
VAR __DS0FilterTable2 =
TREATAS({"2891"}, 'EMEA LISC'[Business_Unit])
VAR __DS0Core =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'EMEA LISC'[Item_Number],
'EMEA LISC'[Business_Unit],
'EMEA LISC'[JULIANREQUESTDATE_WID],
'Request Date'[Date],
__DS0FilterTable,
__DS0FilterTable2,
"CountRowsEMEA_LISC", CALCULATE(COUNTROWS('EMEA LISC'))
)
),
OR(
OR(
OR(
NOT(ISBLANK('EMEA LISC'[Item_Number])),
NOT(ISBLANK('EMEA LISC'[Business_Unit]))
),
NOT(ISBLANK('EMEA LISC'[JULIANREQUESTDATE_WID]))
),
NOT(ISBLANK('Request Date'[Date]))
)
)
),
"'EMEA LISC'[Item_Number]", 'EMEA LISC'[Item_Number],
"'EMEA LISC'[Business_Unit]", 'EMEA LISC'[Business_Unit],
"'EMEA LISC'[JULIANREQUESTDATE_WID]", 'EMEA LISC'[JULIANREQUESTDATE_WID],
"'Request Date'[Date]", 'Request Date'[Date]
)
VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'EMEA LISC'[Item_Number],
1,
'EMEA LISC'[Business_Unit],
1,
'EMEA LISC'[JULIANREQUESTDATE_WID],
1,
'Request Date'[Date],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
'EMEA LISC'[Item_Number],
'EMEA LISC'[Business_Unit],
'EMEA LISC'[JULIANREQUESTDATE_WID],
'Request Date'[Date]