Ability to determine highest value out of entire data set?

[Anonymous]

I'm currently working through a list of quite requirements that look easy to achieve on the offset but when I'm trying to do it in principle using PowerBI, I'm having some trouble given my basic understanding of the software.

I'm trying to look at the entire data set, and determine which value is the highest overall. I've tried to do this using a MAX statement but it is giving me the maximum value of that category. My data is set up in the following way.

RecordID has a YearQuarter ranging from 2016 Q1 to 2020 Q2, and the values are StandardResults. I need to know which StandardResult is the highest. Looking at Excel, I know that the highest value is 170,000 but I can't seem to figure out how to get this to show in Power BI.

[amitchandak]

@Anonymous , You have use all or allexcept as per need

calculate(max(Table[Value]), all(Table))

calculate(max(Table[Value]), allexcept (Table, Table[Category]))

refer

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[Anonymous]

Hi @amitchandak - thanks for your reply, I don't think I fully understand it however. Using the information you sent over, I've got this:

calcmaxallexcept =
CALCULATE (
    MAX ( ALLDATA[StdResult] ),
    ALLEXCEPT (
        ALLDATA,
        ALLDATA[RecordID],
        ALLDATA[Event Log]
    )
)

However it doesn't seem consistent, I can't figure out why? I've tried to understand ALLEXCEPT but it goes straight over my head, as basic as it is.

I tried another way of doing this which got me the maximum value, but I don't know how to single out just that one item with the RecordID & Event Log?

top1 =
CALCULATE (
    MAX ( 'ALLRES_STDUNITS'[StdResult] ),
    FILTER (
        ALL ( ALLRES_STDUNITS ),
        'ALLRES_STDUNITS'[Event Log]
    )
)

I know the top value is the highest, how would I be able to take what I have and show only this? My background is entirely SQL based, so I know how to do it on one language but I can't figure it out here!

Does this make sense?

[Anonymous · Accepted solution]

I was over complicating this, I was able to achieve it with the TopN filter!