Forum Discussion
Query about generating a new MAX column on the table visual
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
Learner1810 try this:
Col6 = CALCULATE ( MAX ( 'Table'[Col5] ), ALLEXCEPT ( 'Table', 'Table'[Col3] ) )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.
4 Replies
- parry2k
Super User
Learner1810 try this:
Col6 = CALCULATE ( MAX ( 'Table'[Col5] ), ALLEXCEPT ( 'Table', 'Table'[Col3] ) ) 
v-ssriganeshCommunity Support
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.- 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,
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.