How to get the highest value on the same date

[Roland74]

Hi,

im struggling with this case. I've got this table:

(For the month June everything is the same, except the 'ResultID') 

For a selected month i need the highest ResultID, i.e. for June i want the line with ID1841, but when i select August, June is also the highest ID. I've tried several formules, now i've got:

But i always get for june both lines:

Trying to understand what i'm doing wrong en why?

[Roland74 · Accepted solution]

After some trial-and-error i found the solution, which was quite simple.

MaxID_Calc2 = CALCULATE(

                MAX(factProjectPreclosureResult[ProjectPreclosureResultID]),

                ALL(factProjectPreclosureResult[ProjectPreclosureResultID]),

                ALL(factProjectPreclosureResult[PostingDateID])

                )

[v-xuxinyi-msft]

Hi @Roland74 

Thanks for the reply from bhanu_gautam and Greg_Deckler, please allow me to provide another insight:

I added a row of data to the sample data you gave, as follows:

1. Create a calculated column as a slicer table or filter

Slicer = VALUES('Table'[PostingDateID])

There is no relationship between the two tables.

2. Create a measure as follows

Measure = 
VAR _selected = SELECTEDVALUE('Slicer'[PostingDateID])
VAR _max = CALCULATE(MAX([ProjectPreclosureResultID]), FILTER(ALL('Table'), [PostingDateID] = _selected))
RETURN
IF(_selected = BLANK(), 1, IF(MAX([ProjectPreclosureResultID]) = _max, 1, 0))

3. Put the measure into the visual-level filters, set up show items when the value is 1.

Output:

use slicer

or use filter

Best Regards,
Yulia Xu

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

[Roland74]

Hi Yulia,

Thanx for your solution. As i am on direct-query, i can't create that column.

[Greg_Deckler]

@Roland74 Try:

Measure = 
  VAR __Date = MAX( 'factProjectPreclosureResult'[PostingDateID] )
  VAR __Table = FILTER( ALL( 'factProjectPreclosureResult' ), [PostingDateID] = __Date )
  VAR __Result = MAXX( __Table, [ProjectPreclosureResultID] )
RETURN
  __Result

[Roland74]

Hi Greg, 

At least i get one result with your solution, but not the right one.

In my example i show only one jobID, but my real data contains over 2.000 unique ID's.

Now it shows the highest resultID in my table, but that does not belong to the selected JobID.

(I expect 1841)

[Greg_Deckler]

@Roland74 I didn't know about JobID. Try this:

Measure = 
  VAR __Date = MAX( 'factProjectPreclosureResult'[PostingDateID] )
  VAR __JobID = MAX( 'factProjectPreclosureResult'[JobID] )
  VAR __Table = FILTER( ALL( 'factProjectPreclosureResult' ), [PostingDateID] = __Date && [JobID] = __JobID )
  VAR __Result = MAXX( __Table, [ProjectPreclosureResultID] )
RETURN
  __Result

[bhanu_gautam]

@Roland74 , Create a measure to get the highest ResultID for the selected month:

DAX
MaxResultID =
CALCULATE(
MAX(Table[ResultID]),
ALLEXCEPT(Table, Table[Month])
)

Create a measure to check if the ResultID is the highest for the selected month:

DAX
IsMaxResultID =
IF(
Table[ResultID] = [MaxResultID],
1,
0
)

Add the IsMaxResultID measure to the visual level filters.
Set the filter to show only rows where IsMaxResultID is 1.

[Roland74]

Hi Bhanu, 

Thanx for your reaction, but that doesn't seem to do the trick.

Still get the same result