topic Re: DAX Help - replace an aggregated value in DAX Commands and Tips

DAX Help - replace an aggregated value

Coriel-11 — Wed, 19 Jun 2024 14:01:39 GMT

Hi everyone,
I have a table ("Interactions") that looks like this:

Date Team Interactions Source

15/03/2024	Training	328	Emails
06/04/2024	Training	456	Calls
31/05/2024	Membership	123	Emails
31/05/2024	Membership	258	Calls

It's connected to my "Calendar" (i.e. Date) Table.

I have a visual (line graph) which has month along the x-axis and sums interactions on the Y axis with the Source in the Legend.

Unfortunately I need to replace the figure for Emails from Training during May 2024 with 4106.

I tried to do this with an IF statement but it doesn't aggregate properly, it replaces the point on the diagram that should be the 4106 + the value for Emails Membership for May, with just the 4106.

Measure = IF(
    MAX(Interactions[Team]) = "Training" &&
    MAX(Interactions[Source]) = "Emails" &&
    YEAR(MAX('Calendar'[Date])) = 2024 &&
    MONTH(MAX('Calendar'[Date])) = 5
    ,
4160,
SUM(Interactions[Interactions])
)

I think that was because I used a MAX value in the filtering (so when faced with Training values combined with Membership, it just takes the MAX - i.e. Training), but I can't work out what I should use instead so there aren't the same problems with aggregating.

Tried a lot of other things as well, without success

Can anyone put me right on this?
Thank you!

Re: DAX Help - replace an aggregated value

ExcelMonke — Wed, 19 Jun 2024 16:12:03 GMT

Hello,

You can consider the following to see if this will get you your intended result:

Measure = VAR _Interactions = IF ( AND ( Interactions[Team] = "Training", Interactions[Source] = "Emails" ), TRUE (), FALSE () ) VAR _Date = IF ( AND ( YEAR ( 'Calendar'[Date] ) = 2024, MONTH ( 'Calendar'[Date] ) = 5 ), TRUE (), FALSE () ) RETURN IF ( AND ( _Interactions, _Date ), 4160, SUM ( Interactions[Interactions] ) )

Re: DAX Help - replace an aggregated value

Coriel-11 — Thu, 20 Jun 2024 08:51:06 GMT

Hi, thanks for you answer, unfortunately, though I still have to add the MAX() function around the fields (or else I get errors) and then that seems to lead me back to where I was before – same results at least. I'd been hoping there was a better alternative to MAX for text, or something like HASONEVALUE().
I really appreciate the suggestion, though.
Matt