MAX return wrong results

[Analitika]

I have Table1

OpNr  | ApNr | Sum | Date

123 | 456 | 300 | 2019-07-17
123 | 856 | 50 | 2019-08-07

Expected result

ApNr | TotalSumByOpNr | MaxDate
456 | 350 | 2019-08-07
856 | 350 | 2019-08-07

Total | 350 | empty (show no dates)

I use

TotalSumByOpNr  = SUMX(VALUES(Table1[OpNr]), CALCULATE(MAX(Table1[Sum])))
MaxDate = MAX(Table1[Date])

Why max return wrong result

Measure = MAX(Table1[Date])

expected 2019-08-07 all

How to dermine dependants by which max function filter values?

[v-alq-msft · Accepted solution]

Hi, @Analitika 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

You may create two measures as below.

TotalSumByOpNr = 
var tab = 
SUMMARIZE(
    Table1,
    Table1[ApNr],
    "Result1",
    var _opnr = SELECTEDVALUE(Table1[OpNr])
    return
    CALCULATE(
        SUM(Table1[Sum]),
        FILTER(
            ALL(Table1),
            Table1[OpNr]=_opnr
        )
    )
)
return
MAXX(
    tab,
    [Result1]
)

MaxDate = 
var tab = 
SUMMARIZE(
    Table1,
    Table1[ApNr],
    "Result2",
    var _opnr = SELECTEDVALUE(Table1[OpNr])
    return
    CALCULATE(
        MAX(Table1[Date]),
        FILTER(
            ALL(Table1),
            Table1[OpNr]=_opnr
        )
    )
)
return
IF(
    ISFILTERED(Table1[ApNr]),
    MAXX(
        tab,
        [Result2]
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

please can you give more information, its hard to understand what you are doing, what result is it returning, how are you running this calculation, do you have some data to share?

[FarhanAhmed]

MAX is evaluating for each row, if you want to exlcude row context in the formula you can use something like this

_MAX Date = CALCULATE (Max (Table[date]),All(Table))

[amitchandak]

@Analitika , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Max seems to work for Grand total.  do you need only one row

Same value in all column

Measure = calculate(MAX(Table1[Date]), allselected(Table1))

or

Measure = calculate(MAX(Table1[Date]), all(Table1))
Appreciate your Kudos.

[Analitika]

I have Table1

OpNr  | ApNr | Sum | Date

123 | 456 | 300 | 2019-07-17
123 | 856 | 50 | 2019-08-07

Expected result

ApNr | TotalSumByOpNr | MaxDate
456 | 350 | 2019-08-07
856 | 350 | 2019-08-07

Total | 350 | empty (show no dates)

I use

TotalSumByOpNr  = SUMX(VALUES(Table1[OpNr]), CALCULATE(MAX(Table1[Sum])))
MaxDate = MAX(Table1[Date])

[amitchandak]

@Analitika , add these columns with OpNr in your visual

calculate(lastnonblankvalue(Table[Date], Sum(Table[Sum])), allexcept(Table[OpNr]))
calculate(max(Table[Date]), allexcept(Table[OpNr]))

[Analitika]

It is not working, as produce dublicates