topic Re: Dynamically Calculating minimum value in DAX Commands and Tips

Dynamically Calculating minimum value

SBethell — Mon, 03 Apr 2023 09:24:57 GMT

I'm trying to visualise the movement of applications thorugh our system, using a dataset that produces a record for every interaction that the application has had with our system, but I only want to be counting the most recent interaction.

Column 4 in the image attached is what I need to get to, showing the smallest number grouped by application, where the Rel Date is at least 6, and at most 100.

Re: Dynamically Calculating minimum value

tamerj1 — Mon, 03 Apr 2023 11:22:32 GMT

Hi SBethell
Please clarify further

Re: Dynamically Calculating minimum value

SBethell — Mon, 03 Apr 2023 11:46:16 GMT

Not sure what exactly you're asking for but in the image above I need to be able to select only the most recent interaction each application has, in the example above I only want to be counting record 3 for application A, and record 2 for application B. I'm currently using:

CALCULATE(MIN(Table[LatestRecord]),ALLEXCEPT(Table,Table[AppID],Table[Rel Date]))

This works when there are no filters on relative date, however when I use a slicer to change the available relatives dates it stops working, and shows each record as the value. If I take out the Table[Rel Date] from the function, it always shows the final record regardless of the relative date.

Re: Dynamically Calculating minimum value

tamerj1 — Mon, 03 Apr 2023 14:04:48 GMT

SBethell

Please try the following measure

=
CALCULATE (
MIN ( Table[LatestRecord] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[AppID] )
)

Re: Dynamically Calculating minimum value

SBethell — Tue, 04 Apr 2023 09:47:30 GMT

Just tried it and I'm still getting the same issue where it works for when I have nothing on my slicer but as soon as I adjust the slicer, even if it doesn't impact the application I'm using as a test it shows each record as the minimum again.

Re: Dynamically Calculating minimum value

tamerj1 — Tue, 04 Apr 2023 09:51:22 GMT

SBethell
Would you please provide some screeshots to clarify your point?

Re: Dynamically Calculating minimum value

SBethell — Tue, 11 Apr 2023 07:18:48 GMT

Image 1 is when there is no filter on date, LastSSCandInteraction is the measure Ihad at the start, measure is the the DAX you suggested. Image 2 is when I have a filter on relative date which does not affect any of the interactions on the application

Re: Dynamically Calculating minimum value

tamerj1 — Tue, 11 Apr 2023 08:00:05 GMT

SBethell
Not sure if I fully understand, However, please try

= CALCULATE ( MIN ( Table[LatestRecord] ), ALL ( 'Table' ), VALUES ( 'Table'[AppID] ) )

= CALCULATE ( MIN ( Table[LatestRecord] ), ALLEXCEPT ( 'Table', 'Table'[AppID] ) )

Re: Dynamically Calculating minimum value

SBethell — Wed, 12 Apr 2023 07:15:24 GMT

Unfortunately still no dice, but I realised I didn't share the formula I've worked out in excel. MINIFS($B$2:$B$21,$C$2:$C$21,">="&$I$2,$C$2:$C$21,"<="&$J$2,$A$2:$A$21,A2)