Forum Discussion
MAXIF CALCULATED COLUMN
I need to implement MAXIF as a calculated column. It is piece of cake in Excel as shows below but I am not able to get exact DAX formula in POWER BI.
I have 3 columns and the MAXIF formula in Excel matches the ID as well as value column, the result is the max month where ID is same and value is "-1" (i.e. June for Id=101 AND May for id=201),
In Power BI I have tried the below but it does not consider the ID at the row level and gives June for both the id's ( see PBI snapshot) How can I achieve the results shown in Excel snaphsot but in PBI?
Max Month where ID is minus 1 = calculate(max(Test[Fiscal Month]),FILTER(Test,Test[ID]=Test[ID] && Test[Value] = -1))
Here is the sample Data:
ID Fiscal Month Value Max Month where ID =1
101 4/1/2020 1 6/1/2020
101 6/1/2020 -1 6/1/2020
201 12/1/2019 1 5/1/2020
201 2/1/2020 -1 5/1/2020
201 2/1/2020 1 5/1/2020
201 5/1/2020 -1 5/1/2020
Hello @jayantkodwani ,
You can add an ALLEXCEPT() as shown below:
Column = CALCULATE ( MAX ( 'Table'[Fiscal Month] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Value] = -1 ) )Hello @v-xuding-msft
here's my solution (see figure)
Max Month where ID is minus 1 = CALCULATE( MAX('Table'[Fiscal Month]), FILTER( ALLEXCEPT( 'Table', 'Table'[ID] ), 'Table'[Value] = -1 ) )Greetings FrankAT
5 Replies
jayantkodwani - According to the Excel to DAX Translation reference: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991
MAXIF found here:
https://community.powerbi.com/t5/Community-Blog/L-M-Excel-to-DAX-Translation/ba-p/1061081
The DAX equivalent is:
MAXX(FILTER(... && ...)...) or CALCULATE(MAX(),FILTER(... && ...))
Thanks Greg, appreciate your timely response, While the list of Excel to DAX is highly beneficial, it does not work directly for me. If we look at the initial PBI snapshot, I already tried the DAX equivalent you mentioned but it was not working in the calculated column giving same month values for all the rows. The reason seems to be the query context. So, I finally solved it by a workaround
Step 1 - Created a Meaure
Step 2 - Created a calculated table to use the measure only with ID
Step 3 - Used LOOKUPVALUEHello @jayantkodwani ,
You can add an ALLEXCEPT() as shown below:
Column = CALCULATE ( MAX ( 'Table'[Fiscal Month] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Value] = -1 ) )
