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.
Please help! My brains hurts 🙄
I'd like to find the most recent COMPANY assigned to each FILE listed below.
The most recent COMPANY (LATEST_COMPANY) should be calculated by:
1. Finding the most recent FILE_PERIOD_NUMBER for each FILE group and
2. Reporting back the COMPANY of the most recent FILE_PERIOD_NUMBER in the LATEST_COMPANY column
YEAR | FILE | FILE_PERIOD_NUMBER | COMPANY | LATEST_COMPANY |
2001 | 123 | 123-01 | Company1 | Company2 |
2002 | 123 | 123-02 | Company1 | Company2 |
2003 | 123 | 123-03 | Company2 | Company2 |
2002 | 234 | 234-02 | Company5 | Company3 |
2003 | 234 | 234-03 | Company4 | Company3 |
2004 | 234 | 234-04 | Company3 | Company3 |
How would I do this by either creating a column in Power Query or DAX?
So far, all I've been able to do is work out the most recent FILE_PERIOD_NUMBER using the following:
Max_File_Period_Number = CALCULATE(max(Files[FILE_PERIOD_NUMBER]),ALLEXCEPT(Files,Files[FILE]))
Many thanks in advance
Solved! Go to Solution.
You can use the following dax to create a measure:
last company2 =
VAR max1 =
CALCULATE (
MAX ( 'Table1'[year] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[FILE] = MAX ( 'Table1'[FILE] ) )
)
VAR last =
CALCULATE (
MAX ( 'Table1'[COMPANY] ),
FILTER (
ALL ( 'Table1' ),
'Table1'[YEAR] = max1
&& 'Table1'[FILE] = MAX ( 'Table1'[FILE] )
)
)
RETURN
last
then create a new column base on the measure:
last company = [last company2]
Final you will see like below:
click here to download if you need!
Wish it is helpful for you!
Best Regard
Lucien Wang
You can use the following dax to create a measure:
last company2 =
VAR max1 =
CALCULATE (
MAX ( 'Table1'[year] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[FILE] = MAX ( 'Table1'[FILE] ) )
)
VAR last =
CALCULATE (
MAX ( 'Table1'[COMPANY] ),
FILTER (
ALL ( 'Table1' ),
'Table1'[YEAR] = max1
&& 'Table1'[FILE] = MAX ( 'Table1'[FILE] )
)
)
RETURN
last
then create a new column base on the measure:
last company = [last company2]
Final you will see like below:
click here to download if you need!
Wish it is helpful for you!
Best Regard
Lucien Wang
That's AWESOME - taught me so much...thank you!
Would you know how to do this as a measure? - it's taking a really long time to run
I am using the [Custom] column as the original column is not number, and your max
Last_Company =
VAR CurFile =
SELECTEDVALUE ( Table1[FILE] )
VAR maxFilePeriod =
CALCULATE ( MAX ( Table1[Custom] ), ALLEXCEPT ( Table1, Table1[FILE] ) )
RETURN
MAXX (
FILTER ( ALL ( Table1 ), [Custom] = maxFilePeriod && Table1[FILE] = CurFile ),
Table1[COMPANY]
)
Vera thanks so much for replying back 🙂
I'm having trouble getting this working - I end up with blank values.
Can I confirm, what value is in the [custom] column?
I thought you meant to change it to [FILE_PERIOD_NUMBER], but as I've said this just produces a blank result.
Thanks again for your help
It is the last 2 digits of your original column, I had this column in the M solution. You can also wrap RIGHT([your original column],2) to replace my [custom] column. Have a try:)
Hi @Vera_33 - I still get a blank column...I must be doing something wrong here.
Here's what I've done:
https://filebin.net/g0dm5sp2qrssijys/Testing.pbix?t=449jz9vp
Thanks again for your help
The one you wrote is a calculated column, not a measure...if you need a column
Column =
VAR CurFile = Table1[FILE]
VAR maxFilePeriod =
CALCULATE ( MAX ( Table1[Custom] ), ALLEXCEPT ( Table1, Table1[FILE] ) )
RETURN
MAXX (
FILTER ( ALL ( Table1 ), [Custom] = maxFilePeriod && Table1[FILE] = CurFile ),
Table1[COMPANY]
)
This is one way to do in Power Query, adding a column as a result, paste it in Advanced Editor
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMjIwMFTSUTI0MoaQumCuc35uQWJepaFSrA5YiRGqEiMsSoxRlRgjlBghm2JkbAIhUUwxRTYFSQmSKSYwJSaoSkwQSoyVYmMB",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [YEAR = _t, FILE = _t, FILE_PERIOD_NUMBER = _t, COMPANY = _t]
),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.End([FILE_PERIOD_NUMBER], 2)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom", {{"Custom", Int64.Type}}),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"FILE"},
{
{"max", each List.Max([Custom]), type nullable number},
{
"allrows",
each _,
type table [
YEAR = nullable text,
FILE = nullable text,
FILE_PERIOD_NUMBER = nullable text,
COMPANY = nullable text,
Custom = nullable number
]
}
}
),
#"Merged Queries" = Table.NestedJoin(
#"Grouped Rows",
{"FILE", "max"},
#"Changed Type",
{"FILE", "Custom"},
"Grouped Rows",
JoinKind.LeftOuter
),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(
#"Merged Queries",
"Grouped Rows",
{"COMPANY"},
{"Last_COMPANY"}
),
#"Removed Other Columns" = Table.SelectColumns(
#"Expanded Grouped Rows",
{"allrows", "Last_COMPANY"}
),
#"Expanded allrows" = Table.ExpandTableColumn(
#"Removed Other Columns",
"allrows",
{"YEAR", "FILE", "FILE_PERIOD_NUMBER", "COMPANY"},
{"YEAR", "FILE", "FILE_PERIOD_NUMBER", "COMPANY"}
)
in
#"Expanded allrows"
@Vera_33 That's AWESOME - taught me so much...thank you!
Would you know how to do this as a measure? - it's taking a really long time to run
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.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |