none
Left Outer Join in SSAS Tabular Model RRS feed

  • Question

  • Hi,

    I have below scenario. Table1 and Table2 relationship is 1 to Many in SSAS Tabular model.

    Table 1         Table 2  
    Product ID Prod Name SalesAmount     Product ID ProductSoldLocations
    1    A 100     1     US
    2    B 50     2     UK
    3    C 250     1     UK

    When the cube is live connected from Power BI, below behavior is seen

    1. When all the fields from Table1 are selected, i get to see all three products in tabular grid.

    2. When I pull in ProductSoldLocations from Table2, Product ID 3 is getting filtered and not visible. I get to see only Products 1 and 2. In general, user expectation is to see all three products with PorductSoldLocation as NULL for ProductID 3.

    This is only an example table scenario, in there are many columns in both the tables.

    Please let me know if this is intended behviour or am i missing anything.

    Thanks
    Manoj

    Wednesday, November 20, 2019 4:45 PM

Answers

  • Hi manalla,

    Thanks for your clarification.

    The logic of the filter is based on 1 to many relationship between table1 and table2. Therefore the record for productID 3 in table 2 is filtered.

    If you need to achieve that, here are two methods for your reference:

    1. Add one record (Product ID 3, ProductSoldLocations null) to the table 2;

    2. Use DAX expression to create a calculated table with expected ProductID,ProductSoldLocations.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by manalla Friday, November 22, 2019 3:58 PM
    Friday, November 22, 2019 8:04 AM

All replies

  • Hi Manoj,

    Yes, it is expected behavior. If you pull the column "Product ID" from table 1 and the column "ProductSoldLocations" from table2, the Product ID 3 would not be filtered.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 21, 2019 2:43 AM
  • Hi Will,

    In my case when i pull ProductID from table1 and "ProductSoldLocations" from table2, the Product ID 3 is getting filtered. I tried to explain this in Point 2 in previous message.

    What users want to see is

    Product  ID ProductSoldLocations
    1 US
    1 UK
    2 UK
    3 NULL

    But what i am seeing is 

    Product  ID ProductSoldLocations
    1 US
    1 UK
    2 UK


    Thanks

    Manoj

    Thursday, November 21, 2019 6:33 PM
  • Hi manalla,

    Thanks for your clarification.

    The logic of the filter is based on 1 to many relationship between table1 and table2. Therefore the record for productID 3 in table 2 is filtered.

    If you need to achieve that, here are two methods for your reference:

    1. Add one record (Product ID 3, ProductSoldLocations null) to the table 2;

    2. Use DAX expression to create a calculated table with expected ProductID,ProductSoldLocations.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by manalla Friday, November 22, 2019 3:58 PM
    Friday, November 22, 2019 8:04 AM