The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to find the earliest occurances of the highest value in a given column.
Here is the initial state of the data:
Item | Cost | Date |
a1 | 1 | 01/01/2022 |
a1 | 3 | 01/05/2022 |
a1 | 1 | 01/06/2022 |
a1 | 2 | 01/03/2023 |
a1 | 1 | 01/07/2023 |
b2 | 2 | 01/03/2021 |
b2 | 3 | 01/02/2022 |
b2 | 1 | 01/04/2022 |
b2 | 1 | 01/07/2022 |
b2 | 3 | 01/09/2022 |
b2 | 1 | 01/11/2022 |
As we can see Item a1 had it's higest cost on 01/05/2022.
Item b2 had two instances of highest cost, one in 01/02/2022, and the other on 01/09/2022. I want to capture only the first one.
So from this data I want to get the following result:
Item | Cost | Date |
a1 | 3 | 01/05/2022 |
b2 | 3 | 01/02/2022 |
I have so far used a query level action, first sorting Date Ascending, then sorting Cost Descending before deduping the whole thing, as that's a logic that works in Excel. I also used buffer.table when sorting to ensure all data is captured in both sorts.
But I also want to verify this data, so I am looking at making a calcualted column against the original version of this table.
Can anyone help with how that should look like?
As an update, I tried deduping based on the columns that were causing me issues, but it didn't help.
Still getting the above error. Any ideas?
create a temporary table like
Tmp table =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Item], 'Table'[Date] ),
"@num rows", CALCULATE ( COUNTROWS ( 'Table' ) )
)
that should help you identify any remaining duplicates.
You can create a calculated table like
My Table =
INDEX(
1,
'Table',
ORDERBY( 'Table'[Cost], DESC, 'Table'[Date], ASC),
PARTITIONBY( 'Table'[Item]),
MATCHBY( 'Table'[Item], 'Table'[Date])
)
Just for reference, it needed a Blank argument as well, I chose "Default".
I am getting an error though:
Despite of MatchBy columns being specified, duplicated rows are encountered in INDEX's Relation parameter. This is not allowed. The current operation was cancelled because another operation in the transaction failed.
I think this might be because some Items might have the same date and rating twice, will try to dedupe that part on query level and then run this again.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
93 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |