MAXIF CALCULATED COLUMN

[jayantkodwani]

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

[v-xuding-msft · Accepted solution]

Hello @jayantkodwani ,

You can add an ALLEXCEPT() as shown below:

Column =
CALCULATE (
    MAX ( 'Table'[Fiscal Month] ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Value] = -1 )
)

[Greg_Deckler]

@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(... && ...))

[jayantkodwani]

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 LOOKUPVALUE

[v-xuding-msft · Accepted solution]

Hello @jayantkodwani ,

You can add an ALLEXCEPT() as shown below:

Column =
CALCULATE (
    MAX ( 'Table'[Fiscal Month] ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Value] = -1 )
)

[FrankAT · Accepted solution]

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

[v-xuding-msft]

Hi @FrankAT ,

Thanks for the solution. But, I'm not the questioner. And your formula is same as mine.🙂