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
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.
Solved! Go to 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.
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.
Perfect, that works for what I need it to do.
Thanks!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
58 | |
19 | |
12 | |
11 | |
10 |