topic Re: How to get the highest value on the same date in DAX Commands and Tips

How to get the highest value on the same date

Roland74 — Mon, 28 Oct 2024 12:18:26 GMT

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?

Re: How to get the highest value on the same date

bhanu_gautam — Mon, 28 Oct 2024 12:29:11 GMT

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.

Re: How to get the highest value on the same date

Greg_Deckler — Mon, 28 Oct 2024 12:30:29 GMT

Roland74 Try:

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

Re: How to get the highest value on the same date

Roland74 — Mon, 28 Oct 2024 14:01:05 GMT

Hi Bhanu,

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

Still get the same result

Re: How to get the highest value on the same date

Roland74 — Mon, 28 Oct 2024 14:03:54 GMT

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)

Re: How to get the highest value on the same date

Greg_Deckler — Mon, 28 Oct 2024 18:35:36 GMT

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

Re: How to get the highest value on the same date

Anonymous — Wed, 30 Oct 2024 09:46:53 GMT

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.

Re: How to get the highest value on the same date

Roland74 — Thu, 07 Nov 2024 07:39:48 GMT

Hi Yulia,

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

Re: How to get the highest value on the same date

Roland74 — Thu, 07 Nov 2024 07:41:28 GMT

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

)