Forum Discussion

Learner1810's avatar
Learner1810
New Member
9 months ago
Solved

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 c...
Need Help
  • parry2k's avatar
    parry2k
    9 months ago

    Learner1810 try this:

     

    Col6 = 
CALCULATE ( 
    MAX ( 'Table'[Col5] ),
    ALLEXCEPT ( 'Table', 'Table'[Col3] )
)
  • v-ssriganesh's avatar
    v-ssriganesh
    9 months ago

    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's avatar
parry2k
Super User
9 months ago

Learner1810 try this:

 

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