Counting number of unique deliveries per day (SUPPLY CHAIN)

[JGreenSEAS]

I'm working on a Data Model that I created to replicate an excel model, but what I'd like to do is create a measure that will display the number of distinct values for the number of deliveries or "drops" that a customer requires on a certain day. Customers will potentially have multiple invoices on a specific day, and the date creates each line item on sales invoices as it's own individual row, so my invoice number repeats over multiple rows. I'm already using a DISTINCTCOUNT measure to count the total number of unique invoices into it's own measure so that it can be used to summarize set time periods (daily, weekly, monthly, etc.). 

The new measure would count the number of "Drops" (the distinct count of SOPNUMBE) that a given customer ID (CUSTNAME) consumes within a given date (Req Ship Date), defined on a per delivery route (SHIPMTHD) basis. 

Req Ship Date	SOPNUMBE	DOCID	CUSTNAME	ITEMNMBR	QTYREMAI	UOFM	SHIPMTHD
45355	0828834	INV	Customer1	10S710PM62	600	Kg	RT21
45355	0828834	INV	Customer1	10S7F56102	510	Kg	RT21
45355	0828957	INV	Customer2	2600F49101	20.05	LB	RT21
45355	0828957	INV	Customer2	C5D3M08101	1	LB	RT21
45355	0829528	INV	Customer3	10S720F102	5040	LB	CUSTOMER P/U
45355	0829532	INV	Customer4	10S752F7S5	10	LB	RT6
45355	0829532	INV	Customer4	19C3M28M62	1	CS10	RT6
45355	0829532	INV	Customer4	23D1F04101	5	LB	RT6
45355	0829532	INV	Customer4	2600F49101	11.2	LB	RT6
45355	0829532	INV	Customer4	C500M12TD2	1	CS30	RT6
45355	0829532	INV	Customer4	O4P1M05101	6	TUB	RT6
45355	0829532	INV	Customer4	P3W4P06A72	1	CS10	RT6
45355	0829532	INV	Customer4	P3W4P63A72	1	cs12	RT6
45355	0829532	INV	Customer4	R1P1F13101	5	LB	RT6
45355	0829538	INV	Customer4	10A7P12WF2	3	CS41	RT6
45355	0829538	INV	Customer4	10S7L43WF2	1	CS10	RT6
45355	0829538	INV	Customer4	3900D12WF2	2	CS	RT6

In the above sample of data, my Total Orders measure would return a value of "2" for Customer4 as they had two distinct invoices, but the Total Drops measure would return a value of "1" they have two unique invoice #'s but both have the same date and same shipping method.

We did this previous in SQL for a report, but was done in a field expression that allowed for the distinct count of multiple variables.

SQL code is: "CountDistinct(Fields!CUSTNMBR.Value + Fields!SHIPMETHD.Value + Fields!ReqShipDate.Value.ToString()"

Any help is greatly appreciated, I've not been able to find any similar situations online. 

[lbendlin · Accepted solution]

There's no need to compute the Total Drops as the grouping already gives you that answer.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZRLS8QwFIX/Sul6GO8jN2mXY2tAnDqhj5nFMCsRVyI4+v9NX5QqjFOJgYQki++cnNzkeIyVsEi8iiGhJGEVzZrfv3/cf9vJPs8fb6/P7+jnCJVBcIUmv9AAfnx48UNZE8anVQi8FY3Q4gUv41MxC/Atkbxjq1KEVotgDS1qextOIJOcC0h6AbwET4WSBXAewiGwQzigYORnTVXvirsycjfNTx2mBTpq0BGyppJuMZ1C/xEezehpxgUlfQW1EWVVpxEIT5yjBdXfgAT2PqsfxDUF5mcCUCDV+ZQNB8xmpxwWIL197XvdBDTv+KAc6I35n4vt8Jon/NMZKRy+RIcW+aq6uebhzmseNsYhHWxrmLtoFIbEV2aruMf/mvxiPKcA+ei+++WqEX76Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Req Ship Date" = _t, SOPNUMBE = _t, DOCID = _t, CUSTNAME = _t, ITEMNMBR = _t, QTYREMAI = _t, UOFM = _t, SHIPMTHD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Req Ship Date", Int64.Type}, {"SOPNUMBE", Int64.Type}, {"DOCID", type text}, {"CUSTNAME", type text}, {"ITEMNMBR", type text}, {"QTYREMAI", type number}, {"UOFM", type text}, {"SHIPMTHD", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"CUSTNAME", Text.Trim, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Req Ship Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Req Ship Date", "CUSTNAME", "SHIPMTHD"}, {{"Orders", each List.Count(List.Distinct(_[SOPNUMBE])), Int64.Type}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[JGreenSEAS]

Perfect, that works for what I need it to do.

Thanks!