Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JGreenSEAS
Regular Visitor

Counting number of unique deliveries per day (SUPPLY CHAIN)

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 DateSOPNUMBEDOCIDCUSTNAMEITEMNMBRQTYREMAIUOFMSHIPMTHD
453550828834              INV            Customer110S710PM62600KgRT21
453550828834              INV            Customer110S7F56102510KgRT21
453550828957              INV            Customer22600F4910120.05LBRT21
453550828957              INV            Customer2C5D3M081011LBRT21
453550829528              INV            Customer310S720F1025040LBCUSTOMER P/U
453550829532              INV            Customer410S752F7S510LBRT6
453550829532              INV             Customer419C3M28M621CS10RT6
453550829532              INV             Customer423D1F041015LBRT6
453550829532              INV             Customer42600F4910111.2LBRT6
453550829532              INV             Customer4C500M12TD21CS30RT6
453550829532              INV             Customer4O4P1M051016TUBRT6
453550829532              INV             Customer4P3W4P06A721CS10RT6
453550829532              INV             Customer4P3W4P63A721cs12RT6
453550829532              INV             Customer4R1P1F131015LBRT6
453550829538              INV             Customer410A7P12WF23CS41RT6
453550829538              INV             Customer410S7L43WF21CS10RT6
453550829538              INV             Customer43900D12WF22CSRT6

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. 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

lbendlin_0-1715733025519.png

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.

 

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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

lbendlin_0-1715733025519.png

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.

 

 

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

 

Thanks!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.