Forum Discussion
Solved
Cross-Sell Analysis
Hi team, trying to do some cross-selling analysis and I have a Sales table with columns ORDER, PRODUCT, AMOUNT and need some suggestions to build the model and create DAX. No other table with Produ...
Hi gdecome
It's not able to combine the 2 logics into one calculation, if you'd like to get the counts of which only have related prod items, using below measure:
Measure = var a = SELECTEDVALUE('Table'[Prod]) var b = SELECTEDVALUE('Table 2'[Prod]) var t =DISTINCT( UNION(ROW("Prod",a),ROW("Prod",b)) ) Return COUNTROWS(FILTER(all('Table'[Order]),CALCULATE(var t2=CALCULATETABLE(VALUES('Table'[Prod]),ALLSELECTED('Table'[Prod])) return COUNTROWS ( t2 ) = COUNTROWS ( t ) && ISEMPTY ( EXCEPT ( t, t2 ) ))))If you'd like to get the counts of which Inclusive of prod items, use this one:
Measure 2 = var t1 = SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Prod]=SELECTEDVALUE('Table 2'[Prod])),"order",[Order]) Return CALCULATE(DISTINCTCOUNT('Table'[Order]),INTERSECT(t1,VALUES('Table'[Order])) )attached pbix for your ease.
Hi gdecome
It's not able to combine the 2 logics into one calculation, if you'd like to get the counts of which only have related prod items, using below measure:
Measure = var a = SELECTEDVALUE('Table'[Prod])
var b = SELECTEDVALUE('Table 2'[Prod])
var t =DISTINCT( UNION(ROW("Prod",a),ROW("Prod",b)) )
Return COUNTROWS(FILTER(all('Table'[Order]),CALCULATE(var t2=CALCULATETABLE(VALUES('Table'[Prod]),ALLSELECTED('Table'[Prod])) return COUNTROWS ( t2 ) = COUNTROWS ( t )
&& ISEMPTY ( EXCEPT ( t, t2 ) ))))
If you'd like to get the counts of which Inclusive of prod items, use this one:
Measure 2 = var t1 = SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Prod]=SELECTEDVALUE('Table 2'[Prod])),"order",[Order])
Return
CALCULATE(DISTINCTCOUNT('Table'[Order]),INTERSECT(t1,VALUES('Table'[Order]))
)
attached pbix for your ease.
Hi v-diye-msft , thank you very much for your support.