Forum Discussion

Coriel-11's avatar
Coriel-11
Resolver II
2 years ago

DAX Help - replace an aggregated value

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

Date Team Interactions Source

 

15/03/2024Training328Emails
06/04/2024Training456Calls
31/05/2024Membership123Emails
31/05/2024Membership258Calls

 

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!

Need Help

2 Replies

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual
    2 years ago

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

     

    • Coriel-11's avatar
      Coriel-11
      Resolver II
      2 years ago

      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