Max function in multiple sheets

[GregMeskens]

Dear all,

I have a large amount of files consolidated in power bi. I would like in query editor to return the max value of a column per period. So max value in column 3 per item/period.

I cannot make a good function in DAX quite new to it. Any easy suggestions?Much appreciated.

Column 1	Column 2	Column 3
Period 1	Item 1	Value 1
Period 2	Item 1	Value 2
Period 3	Item 1	Value 3
Period 4	Item 1	Value 4
Period 5	Item 1	Value 5
Period 6	Item 1	Value 6
Period 7	Item 1	Value 7
Period 1	Item 2	Value 1
Period 2	Item 2	Value 2
Period 3	Item 2	Value 3
Period 4	Item 2	Value 4
Period 5	Item 2	Value 5
Period 6	Item 2	Value 6
Period 7	Item 2	Value 7

[v-ljerr-msft · Accepted solution]

Hi @GregMeskens,

In addition, if you want to add a column to your table to get the max value of a column per period/item, then the the formulas(DAX) below should work in this scenario. 

Column = CALCULATE(MAX(Table1[Column 3]),ALLEXCEPT(Table1,Table1[Column 1]))

or

Column = CALCULATE(MAX(Table1[Column 3]),ALLEXCEPT(Table1,Table1[Column 2]))

Note: just replace "Table1" with your real table name.

Regards

[MFelix]

Hi @GregMeskens,

Don't know if you need to have the max used in another calculations/visuals but if you are just looking for the max to put in a table as you show in your image you can add the 3 columns to the Table visual and for value select the MAX this will give you what you are looking for.

If you want to use a measure this are calculated based on the context of the visuals so if you add the measure below to your table it also will give you the expect result:

Max_Value = Max(Table[Value])

See below the results in the visuals with both options measure and Summary Max

Regards,

MFelix