Forum Discussion

MarkCBB's avatar
MarkCBB
Helper V
10 years ago
Solved

Items not found

Hi there,   I have 3 tables: Product (ProductID, Product Name) Stores (StoreID, Sotre Name) Sales (Fact Table)   I need to create a list that contains Products that are not in a store, the fac...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    It does the right thing, just that you need to connect the code to your tables. Either like this:

     

    let
    Sales = SALES,
    Product = P,
    Store = S,
    Partition = Table.Group(Sales, {"StoreID"}, {{"Partition", each Table.NestedJoin(_,{"ProductID"},Product,{"Product​ID"},"NewColumn",JoinKind.RightAnti)}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"NewColumn"}, {"NewColumn"}),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Expanded Partition", "NewColumn", {"ProductID"}, {"ProductID"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([ProductID] <> null))
in
    #"Filtered Rows"

     

    or directly:

    let
    Partition = Table.Group(SALES, {"StoreID"}, {{"Partition", each Table.NestedJoin(_,{"ProductID"},P,{"ProductID"},"NewColumn",JoinKind.RightAnti)}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"NewColumn"}, {"NewColumn"}),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Expanded Partition", "NewColumn", {"ProductID"}, {"ProductID"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([ProductID] <> null))
in
    #"Filtered Rows"

     

     

MarkCBB's avatar
MarkCBB
Helper V
10 years ago

Hello ImkeF

 

Thank you for the Sharing that Video, it was very helpful. 

 

After watching the video I realised that DAX might be a better approach to the items not found. Currently this is taking well over 4 hours to run - But I have no idea where to start. (I upgraded my RAM to 64G hoping that that would solve the 4 hour wait but it didnt really make an impact).

 

At the moment my machine is just not coping with the Query, and the more data i am adding to it the slower this is going?

Can something like this be done in DAX, or do I need to change the date range of the Sales table to be only X months insteal of all months. From a business few 3 months would be the min.

 

 

 

 

 

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    Hi Mark,

    if you have a performance issue here, you should shrink your SALES-table to distinct values on Product ID only and buffer the result. Then move on with the same technique from there.