Forum Discussion
Community ChampionDAX Question - Customers who purchase again after a return.
Likely more elegant solutions than this, but you could create a calculated column:
SaleAfterReturn = IF(MAXX(FILTER(sales,sales[Customer]=customers[customer]),sales[Date])>MAXX(FILTER(returns,returns[Customer]=customers[customer]),returns[Date]),1,0)
And then a measure:
% Customers After Return = COUNTX(FILTER(customers,customers[SaleAfterReturn]=1),customers[SaleAfterReturn])/COUNT(customers[SaleAfterReturn])
I did this with a customer table:
customers table
customer
1
2
3
and a sales and returns table with Date and Customer. returns and sales and both related to the customers table.
Community ChampionOne thought would be to toss the data into Azure ML and see if you can create an experiment around a 2 category hypothesis of "purchased after return" yes or no. You might quickly gain some valuable insights by looking at the weighting criteria that Azure ML generates when you do something like a 2 class linear support vector. You could also try K-Means Clustering to see if it can give you any insights as well.
Resident Rockstarkcantor, regarding table size, a good heuristic when thinking about the model is that vertical expansion (more rows to existing table) is much cheaper (storage space, RAM pressure, query efficiency) than horizontal expansion (adding columns to a table, or adding additional tables, especially similarly structured tables).
We can dive really deep on this sort of thing if you'd like, but the short version is that each column gets a dictionary. That dictionary maps the minimum sized pointer to the actual values that exist in that column. If you have a field with only two values, the size of an individual row will be 1 bit, and the value of that bit will map to the appropriate value for that field. Adding additional rows only impacts the size of this dictionary if new distinct values are added. So, if you have a sale table and a return table that share a bunch of fields, and also share values in those fields (mapping to the same dimensions), you're probably better off combining them, because then you only maintain one copy of each dictionary, rather than two. This is also why additional columns are expensive.
The above is simplified, but an accurate approximation of the primary storage/compression technology in the Tabular engine powering Power BI.
I don't know if this is helpful or not, but that's what led to my question.
- kcantor
greggybUnfortunately, my return table only shares one field with the sales table and I use them to link the customer look up table. Mostly I cut more columns out than I actually bring in as data. The single calculated column suggested by Greg_Deckler was more elegant than my previous line of reasoning which was creating 3 calculated columns prior to adding a measure. On the up side, IT is on board with PowerBI and PowerPivot so our tables are evolving to be a better match to the tools.
- greggyb
Gotcha, I was thinking that they'd share a number of dimensions and have similar fact fields, e.g. ProductKey, CustomerKey, DateKey, Quantity, UnitPrice all in common and maybe a couple fields that don't match between the two. Sounds like this isn't the case. I'd stick with smoupre's solution then.
