Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am working on a project to assess the sales at our company, based on the invoices. I have a dataset at line item level, and want to assign a category to each invoice number based on the line item with the highest value.
Example data:
Invoice number | Line item | Category | Value | Desired result |
1 | 1 | Bordeaux | 110 | Bordeaux |
1 | 2 | Languedoc | 15 | Bordeaux |
1 | 3 | Alsace | 55 | Bordeaux |
2 | 1 | Bordeaux | 150 | Alsace |
2 | 2 | Alsace | 300 | Alsace |
So basically, the steps are as follows:
Should be quite simple I guess, but I am struggling with the implementation at the moment.
Does anyone know how to do this?
Thanks!
Kirvis
Solved! Go to Solution.
@kirvis with PQ
List.Max(
Table.SelectRows(
CT,
(r) =>
r[Invoice number]
= [Invoice number] and r[Value]
= List.Max(Table.SelectRows(CT, (q) => q[Invoice number] = [Invoice number])[Value])
)[Category]
)
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMlTSAWOn/KKU1MTSChDX0EApVgciZQTEPol56aWpKfnJIDlTuJQxEDvmFCcmpwIZphBxIyymmRrApYyQtRgbACViAQ==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Invoice number" = _t, #"Line item" = _t, Category = _t, Value = _t]
),
CT = Table.TransformColumnTypes(
Source,
{
{"Invoice number", Int64.Type},
{"Line item", Int64.Type},
{"Category", type text},
{"Value", Int64.Type}
}
),
#"Added Custom" = Table.AddColumn(CT, "maxCAT", each List.Max(
Table.SelectRows(
CT,
(r) =>
r[Invoice number]
= [Invoice number] and r[Value]
= List.Max(Table.SelectRows(CT, (q) => q[Invoice number] = [Invoice number])[Value])
)[Category]
))
in
#"Added Custom"
with DAX
_maxCAT =
VAR _partition =
ALLEXCEPT ( 'Table', 'Table'[Invoice number] )
VAR _val =
CALCULATE ( MAX ( 'Table'[Value] ), _partition )
RETURN
CALCULATE ( MAX ( 'Table'[Category] ), 'Table'[Value] = _val, _partition )
Wow, this truly is like magic.
Thanks for the quick reply and for the elegant solution!
@kirvis with PQ
List.Max(
Table.SelectRows(
CT,
(r) =>
r[Invoice number]
= [Invoice number] and r[Value]
= List.Max(Table.SelectRows(CT, (q) => q[Invoice number] = [Invoice number])[Value])
)[Category]
)
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMlTSAWOn/KKU1MTSChDX0EApVgciZQTEPol56aWpKfnJIDlTuJQxEDvmFCcmpwIZphBxIyymmRrApYyQtRgbACViAQ==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Invoice number" = _t, #"Line item" = _t, Category = _t, Value = _t]
),
CT = Table.TransformColumnTypes(
Source,
{
{"Invoice number", Int64.Type},
{"Line item", Int64.Type},
{"Category", type text},
{"Value", Int64.Type}
}
),
#"Added Custom" = Table.AddColumn(CT, "maxCAT", each List.Max(
Table.SelectRows(
CT,
(r) =>
r[Invoice number]
= [Invoice number] and r[Value]
= List.Max(Table.SelectRows(CT, (q) => q[Invoice number] = [Invoice number])[Value])
)[Category]
))
in
#"Added Custom"
with DAX
_maxCAT =
VAR _partition =
ALLEXCEPT ( 'Table', 'Table'[Invoice number] )
VAR _val =
CALCULATE ( MAX ( 'Table'[Value] ), _partition )
RETURN
CALCULATE ( MAX ( 'Table'[Category] ), 'Table'[Value] = _val, _partition )
@smpa01 could it be that the query is very slow when working with several thousand lines? When I try your query on a few lines, it works fine, but as soon as I try it on the complete dataset (a few thousand lines) then it takes forever (up to 5 seconds per line).
Any advice on how to solve this?
@kirvis I am guessing you are asking about PQ and not DAX. If yes, please follow the the known hacks described here
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.