Forum Discussion

Susan_J's avatar
Susan_J
Regular Visitor
5 years ago
Solved

My DirectQuery dataset model appears to be using Inner Joins as default

I have a dataset which is based over dataflow entity tables in a star schema with a single fact table and multiple dimension tables joined to it in many to one relationships.  My Request Date dimension table has no entry for a Date of 0 and yet my fact table contains a row with this Request Date value.

 

When selecting from the fact table the row that has a Request Date of zero and then selecting a column from the Request Date table the row is 'disappearing' as if an Inner Join had been applied.  However, from what I have read it seems that the default join should be an outer join unless the 'Assume Refrential Integrity' checkbox is ticked - and I have not ticked that. 

 

Any ideas?

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    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.⚡

  • Susan_J's avatar
    Susan_J
    Regular Visitor
    5 years ago

    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]