Query about generating a new MAX column on the table visual

[Learner1810]

Hello, I have a table visual on a Power BI Report with data as below. I need to generate column 5 (max of value in Col5 for every combination of Col1+Col2+Col3). Col0 has random values. All columns come from the same table in the Power BI model. 

Col0	Col1	Col2	Col3	Col5	Col6
Abc	01/10/2025	X	Aa	1	5
Abc	02/10/2025	X	Aa	2	5
Xyz	03/10/2025	X	Aa	3	5
Mno	04/10/2025	X	Aa	4	5
nmk	05/10/2025	X	Aa	5	5
BBB	01/10/2025	Y	Bb	1	3
Rqp	02/10/2025	Y	Bb	2	3
kjh	03/10/2025	Y	Bb	3	3

Can someone help please?

Thanks

Y

[v-ssriganesh]

Hello @Learner1810,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @Learner1810,
Thank you for posting your query in the Microsoft Fabric Community Forum.

I have reproduced your scenario in Power BI Desktop using the sample data you shared. By using the below DAX expression, I was able to generate the expected output where the Max Col5 correctly shows the maximum value of Col5 for each unique combination of Col2 + Col3.

Max Col5 =
CALCULATE(
    MAX(DataTable[Col5]),
    ALLEXCEPT(DataTable, DataTable[Col2], DataTable[Col3])
)

The ALLEXCEPT() function keeps the filters on Col2 and Col3 only, allowing the calculation to find the maximum value of Col5 across all dates (Col1) within those groups. For your reference, I have attached a .pbix file.

Thank you, @parry2k for sharing your valuable insights.

Best regards,
Ganesh Singamshetty.

[parry2k]

@Learner1810 try this:

Col6 = 
CALCULATE ( 
    MAX ( 'Table'[Col5] ),
    ALLEXCEPT ( 'Table', 'Table'[Col3] )
)