cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Calculate table with two criterias

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

1 ACCEPTED SOLUTION
Community Champion

@Pbiuserr , create this calculated table:

``````Table Result =
VAR _tbl1 =
VALUES('Table'[ID]),
"@MaxDate" , CALCULATE(MAX('Table'[Date]))
)
VAR _tbl2 =
_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

9 REPLIES 9
Community Champion

@Pbiuserr , create this calculated table:

``````Table Result =
VAR _tbl1 =
VALUES('Table'[ID]),
"@MaxDate" , CALCULATE(MAX('Table'[Date]))
)
VAR _tbl2 =
_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

Post Prodigy

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?

Community Champion

@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.

Post Prodigy

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)?

Community Champion

@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])

Post Prodigy

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

Community Champion

@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

Post Prodigy

I think everything is ok but its data engineering part who messed up 🙂 probably tommorow will get update on that

Community Champion

cool :))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors