topic Re: DAX Challenge in DAX Commands and Tips

DAX Challenge

Anonymous — Tue, 04 Aug 2020 18:22:27 GMT

I'm working on a project where I'll have to find the Number of times a Product has been upsold. For Example, If you buy a car, the sales man will pursue you to buy accesories like All Weather Mats, Roof Rail.. etc. I need to find out how many times All Weather mats are upsold with the particular Car, Like Wise for other accesories as well.

Sales Table:

Order#	Customer#	Item#	Sales$
1	ABC	ITM A	100
1	ABC	ITM B	20
2	BCD	ITM A	100
2	BCD	ITM C	15
2	BCD	ITM D	20
3	DEF	ITM X	200
3	DEF	ITM Y	20
4	FGH	ITM A	100
4	FGH	ITM C	15

Upselling Part Detail

Master Produt	Upsellable Product
ITM A	ITM B
ITM A	ITM C
ITM X	ITM Y

Final Report Will Look SOmething Like this

Report Filter: Master Product : ITM A

Total Orders: 3

Upsold Product	# Of Orders	% Upsold ( Total Order of ITM <> / Total Order)
ITM B	1	1/3 = 33%
ITM C	2	2/3 = 66%

Any Help is much appreciated.

Thanks

Re: DAX Challenge

Greg_Deckler — Tue, 04 Aug 2020 18:37:10 GMT

Anonymous Probably something like:

Measure = VAR __Upsold = MAX('Table2'[Upsellable Product]) VAR __MasterProducts = DISTINCT(SELECTCOLUMNS(FILTER('Table2',[Upsellable Product]=__Upsold),"Master",[Master Product])) VAR __Table1 = SELECTCOLUMNS(FILTER('Table1',[Item#] = __Upsold),"Order#",[Order#]) VAR __Table2 = SELECTCOLUMNS(FILTER('Table1',[Item#] IN __MasterProducts),"Order#",[Order#]) RETURN COUNTROWS(INTERSECT(__Table1,__Table2)) / COUNTROWS(__Table1)

Re: DAX Challenge

amitchandak — Wed, 05 Aug 2020 03:17:51 GMT

Anonymous , refer

Market Basket Analysis
https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/
https://www.daxpatterns.com/usecases/market-basket-analysis/
https://www.mssqltips.com/sqlservertip/5428/market-basket-analysis-in-r-and-power-bi/