TopN from a Summarize Table keeping the filters - Perfoming a Basket Analysis

[andreyinfo]

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.

Figure1Figure2
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).

[technolog]

To solve this, you need to ensure that the filter context from the slicers is passed down to your DAX calculations.

For your fSummarized table, you need to make sure that the filter context is being respected. Instead of creating a calculated table, you might want to create a measure that respects the filter context and then use that measure in your visualizations.

Here's a way to do it:

Create a measure that calculates the top product based on the current filter context:
TopProductMeasure =
VAR TopProduct =
TOPN(
1,
VALUES(DetalheCupons[dProdutos[Produto]]),
CALCULATE(SUM(fDetalheCupons[Qtide]))
)
RETURN
MAXX(TopProduct, dProdutos[Produto])
This measure will give you the top product based on the current filter context. When you use this measure in a table or any other visualization, it will respect the slicer selections.

Now, you can use this measure in your fCombinations:
fCombinations =
FILTER(
CROSSJOIN(
VALUES(fSummarized[Produto]),
SELECTCOLUMNS(
VALUES(fDetalheCupons[Produto]),
"Produto2", [Produto]
)
),
[ProCod] > [Produto2]
)
By using the measure, you're ensuring that the top product is always based on the current filter context. So, when you slice your data using slicers, the measure will recalculate based on the slicer's selection, and your combinations will be based on the top product from the sliced data.