Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I would like to create a table basic on two criteria - max date for ID, and if date is the same, then max value for the date. If it happens that date and value is the same for all lines for certain ID - just take one line for that ID
My initial table
ID | Date | Value |
ID1 | 01.01.2022 | 100 |
ID1 | 01.01.2022 | 150 |
ID1 | 01.02.2022 | 100 |
ID2 | 01.03.2022 | 150 |
ID2 | 01.03.2022 | 100 |
ID3 | 01.04.2022 | 300 |
ID3 | 01.04.2022 | 300 |
ID3 | 01.04.2022 | 300 |
Expected output
ID | Date | Value |
ID1 | 01.02.2022 | 100 |
ID2 | 01.03.2022 | 150 |
ID3 | 01.04.2022 | 300 |
Thank you in advance for help
Solved! Go to Solution.
@Pbiuserr , create this calculated table:
Table Result =
VAR _tbl1 =
ADDCOLUMNS(
VALUES('Table'[ID]),
"@MaxDate" , CALCULATE(MAX('Table'[Date]))
)
VAR _tbl2 =
ADDCOLUMNS(
_tbl1,
"@Value",
VAR _id = 'Table'[ID]
VAR _date = [@MaxDate]
RETURN
CALCULATE(
MAX('Table'[Value]),
'Table'[ID] = _id && 'Table'[Date] = _date
)
)
RETURN
_tbl2
Solution PBIX:
Calculate table with two criterias 2022-08-03.pbix
@Pbiuserr , create this calculated table:
Table Result =
VAR _tbl1 =
ADDCOLUMNS(
VALUES('Table'[ID]),
"@MaxDate" , CALCULATE(MAX('Table'[Date]))
)
VAR _tbl2 =
ADDCOLUMNS(
_tbl1,
"@Value",
VAR _id = 'Table'[ID]
VAR _date = [@MaxDate]
RETURN
CALCULATE(
MAX('Table'[Value]),
'Table'[ID] = _id && 'Table'[Date] = _date
)
)
RETURN
_tbl2
Solution PBIX:
Calculate table with two criterias 2022-08-03.pbix
Thanks, as always excellent help - my one question is that: In which point I add rest columns from the table? I'd need couple of additional columns from that the 'Table'. Is it even possible in that calculation? I think yes, in the _tbl1, create "name", table[column] for each I want to add?
@Pbiuserr my pleasure 🙂
you can add more columns inside addcolumns like you wrote yes. Try and let me know how it went.
Please don't forget to accept the previous message as a solution for community visibility.
Sure thing, already accepted 🙂
I struggle to add more columns, because VALUES(ID) is my table, so i need make a table like SUMMARIZE(Table, ID, Other column, Other column)?
@Pbiuserr oh yes, if you meant to start from a combination of columns then yes,
SUMMARIZE('Table', 'Table'[Column], 'Table'[Other Column], 'Table'[Other Column])
Instead of VALUES('Table'[Column])
When I try with summarize, it again gets me many records for one ID
SUMMARIZE(Table,Table[ID], Table[FlagPayCod],Table[TechnicalFlag], "@Max_Date", CALCULATE(MAX(Table[Creation_Date]) ) )
no idea why, technical flag is always the same, same for ID, the FlagPayCod differs from one line to another but it shouldnt populate more lines
@Pbiuserr i suggest open a new question from scratch and share there a sample data with all the relevant columns for your scenario and explain again there the logic required and send me here the link to the new question
I think everything is ok but its data engineering part who messed up 🙂 probably tommorow will get update on that
cool :))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |