Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
6 years ago
Solved

MAX return wrong results

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?

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Analitika
      Post Prodigy
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

        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]))

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    6 years ago

    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))

     

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    6 years ago
    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?
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.