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

[andreyinfo]

I'm trying to perform a basket analysis. The amount of data is huge, over 30millions lines. The calculation I'm trying to perform is based on a table, created with dax: 

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.
Some would say, short your data, but in fact I need to lengthen it. At least the transactions table(fDetalheCupons), that have the columns (TransactionId, Produto, Quantity,TotalSale, GrossMargin).
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 )

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. But no matter what I select at the slicers I Always get the the same product. Seems that the expression above don't respect the slicers.
THE QUESTION:
How can I create a table to show the top products from transaction table, keeping the filters applieds in the slicers. And then apply the crossjoin expression mentioned before?

Figure2

[andreyinfo · Accepted solution]

Hi @stevedep , the articles that you suggested are pretty good material, and by reading I found a lot of good insights. But, to me is missing a important part.
And here it is. I think that we should let the user to pick a product from a list created by an ABC analysis or whatever analysis that shows the most important products sold, whether in  the company, branch or category.
And by picking a product, the related products would be shown automaticaly ( a table created by the selectedValue crossing the products sold togheter ), ordening by the support, confidence and lift.
And this is my problem, to create a table with the product selected. Everything else is working perfectly, all the statistical analysis, but I have to specify a product typing it's code in the dax formula.
So @stevedep, I think we should close this post and I I'm thinking to open another, but more specifyc question, something like.
"How can I create a table with a single row with the value from a selectedvalue".
What do u think?
I really appreciate your thoughts.

[stevedep]

I suggest to perform the market basket analysis differently,

Perhaps this post helps;

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Need-help-with-DAX/m-p/1149915

[andreyinfo]

@stevedep  Yeah, the post helped, but I'm still thinking that there we need to perform the analysis stressing some variables. In the case at the other post you just want to know how often the products are sold together.
To perform a beatifull, realiable analysis we need to calculate the support, the confidence and the lift. Some literature here: http://www.salemmarafi.com/code/market-basket-analysis-with-r/comment-page-1/ and some more here: https://medium.com/@niharika.goel/market-basket-analysis-association-rules-e7c27b377bd8.

So to deal with the huge amount of data, creating a side filtering table and using the relationship would work. I'll do some adjustments and will see if it worked.

Thanks for now.

[stevedep]

Hi,

For a decent MB analysis I recommend to use R. Esp. because bigger itemsets (more than 2) can be detected. 

p.s. I wrote a blog post comparing the two methods. Also contains lift & support in DAX.

http://bi-and-analytics.blogspot.com/2016/06/market-basket-analysis-association-rule_17.html 

[andreyinfo · Accepted solution]

Hi @stevedep , the articles that you suggested are pretty good material, and by reading I found a lot of good insights. But, to me is missing a important part.
And here it is. I think that we should let the user to pick a product from a list created by an ABC analysis or whatever analysis that shows the most important products sold, whether in  the company, branch or category.
And by picking a product, the related products would be shown automaticaly ( a table created by the selectedValue crossing the products sold togheter ), ordening by the support, confidence and lift.
And this is my problem, to create a table with the product selected. Everything else is working perfectly, all the statistical analysis, but I have to specify a product typing it's code in the dax formula.
So @stevedep, I think we should close this post and I I'm thinking to open another, but more specifyc question, something like.
"How can I create a table with a single row with the value from a selectedvalue".
What do u think?
I really appreciate your thoughts.

[stevedep · Accepted solution]

Hi @andreyinfo ,

I suggest to open a new post. Whereby, if I understand correctly, you are looking to show related items for a product from a table with frequent items sets. 

Please note you can specify output with left hand side and right hand side or specify a frequent itemset as output, in your R code.

Once a product is selected you want to search for the other items in the frequent item set and show these. You show from the itemssets with the highest lift first.

I am currently working on something related, so I have some building blocks, just tag me in your question and I will join you in this one.

Regards Steve