DAX table with all dates, products, clients and largest purchase

[Anonymous]

I'm trying to create a new calculated table from 3 existing tables that has 1 row for each date in my range, each client I have, each product that each client normally buys and the highest single order quantity that they bought each day. For example, if they bought 2 on one order and 3 on another, i'd want it to display 3. If no record of sales are found for that client and product on a given day, it should show that they bought 1 (this is a weird hypothetical, but just go with me). I'm struglling with how to setup the calculated table and the existing table joins.

Here are the tables I'm starting with:

A table of clients and the products that each one buys. You'll see that not all clients buy all products:

Client	Product
A	X
A	Y
A	Z
B	X
B	Y
C	X

A table of dates. To make this example easy, I'll just use 2 dates

Date

4/18/2018

4/19/2018

A table of sales. Not every client buys every product on every day, but they can place multiple orders for the same product on each day.

Date	Client	Product	Qty Sold
4/18/2018	A	X	2
4/18/2018	A	X	3
4/18/2018	A	Z	2
4/18/2018	B	X	2
4/18/2018	B	Y	2
4/18/2018	C	X	2
4/19/2018	A	Z	3
4/19/2018	A	Z	4

Here is the output I'm looking to get:

Note, that for each day each client and each product in the client/product table is listed. If they didn't buy that product on that day, it lists a 1. If they did, it lists the largest order that they placed for that product on that day.

Date	Client	Product	Largest Order
4/18/2018	A	X	3
4/18/2018	A	Y	1
4/18/2018	A	Z	2
4/18/2018	B	X	2
4/18/2018	B	Y	2
4/18/2018	C	X	2
4/19/2018	A	X	1
4/19/2018	A	Y	1
4/19/2018	A	Z	4
4/19/2018	B	X	1
4/19/2018	B	Y	1
4/19/2018	C	X	1

[waltheed · Accepted solution]

I think I may have something for you.

Create a calculated table, with a crossjoin of date and client-products. Then you have all combinations of dates, clients and products. 

Output Table = CROSSJOIN('Clients-Products', 'Dates')

Create a column in this new Output Table, which looks up the right value in the Sales table, by filtering on Date, Client and Product. 

Largest Order = 
	CALCULATE(
		IF(ISBLANK(MAX(Sales[Qty Sold])),
			1,
			max(Sales[Qty Sold])),
		FILTER(	Sales, Sales[Date] = 'Output Table'[Date] && 
			Sales[Client] = 'Output Table'[Client] && 
			Sales[Product] = 'Output Table'[Product]
		)
	)

Then you get this:

So I just used your Date table, the Client-Peroducts table and the Sales table. No extra relationships created. 

Hope this helps. 

[Anonymous]

Thanks Edgar! Worked perfectly.