TopN from a Summarize Table keeping the filters - Perfoming a Basket Analysis
Some description to understand my goal and problem (You can jump to the question). DESCRIPTION : I'm trying to perform a basket analysis. All the concepts are pretty cool, as always are, but when I tried to apply the calculations needed I failed in every effort. The amount of data is huge, over 30millions lines for a single year of sales for a single branch of the company. The calculation I'm trying to perform is based on a table, created with dax, that uses Cross Join to create all the possibilities (when sell product A, what are others sold together). fCombinations = FILTER ( CROSSJOIN ( VALUES ( fDetalheCupons[Produto]), SELECTCOLUMNS ( VALUES ( fDetalheCupons[Produto] ),"Produto2",[Produto] ) ), [ProCod]>[Produto2] ) The expression above generates a table with more than 5 hundred million lines. And Every Column/Measure with this table is imposible to be created due to memory issues (not by my set, which isn't the best, but I use a 6 core processor - Ryzen 5 - 3,6GHz combined to a 16Gb Ram - at the end of the day is my setup, but we need to deliver the results to regular computers that don't have this power of procesation). Some would say, short your data, but in fact I need to lengthen it. At least the transactions table. Than I came with an idea, generating a Summarized Table (even a calculated table) from the transaction table and take the TOP (1 or 2 or 3) products sold. fSummarized = TOPN ( 1, ( SUMMARIZE ( DetalheCupons, dProdutos[Produto], "Qtide", sum ( fDetalheCupons[Qtide] ) ) ), Qtide], DESC ) Then I use this table to cross join the "transactions" table, now the combinations expression is: fCombinations = FILTER ( CROSSJOIN ( VALUES ( fSummarized[Produto]), SELECTCOLUMNS ( VALUES ( fDetalheCupons[Produto] ),"Produto2",[Produto] ) ), [ProCod]>[Produto2] ) And It would solve my problem because now I would deal with max 3 products to combine the sales. IT WORKED FINE, TopN(1,). THE PROBLEM If i don't change the filter context at the transaction table I will always get the same topn 1 product. Then I added some slicers to slice the transaction table (figure 1) . But no matter what I select at the slicers, I Always get the the same product(52081) figure 2. THE QUESTION: How can I create a table to show the top products from the sliced transaction table, keeping the filters applieds in the slicers. In this case, the example, I should get a table with a single row with the product 17432 and not the 52081 (wich is the most sold product, but not on the category selected).
