Forum Discussion

vehloaaron's avatar
vehloaaron
Frequent Visitor
2 years ago

LASTNONBLANK Help

Hi Community - 

 

I am trying to write DAX that will return the name of a marketing campaign, where the date the person responded to the campaign is less than or equal to the date the opportunity was created.  The data is on seperate tables.

 

For my model I have three tables.  A 'Campaign Member' table that holds the marketing response data, a person table (w/ person ID), and a opportunity table.  

 

Here is what I have tried, however it is not pulling as expected.  Looking for some help.

 

Opportunity Create Campaign =
    CALCULATE(LASTNONBLANK('Campaign Member'[Campaign Name],TRUE()),
        FILTER('Campaign Member',
            'Campaign Member'[Person ID]==Opportunities[Person ID] &&
            'Campaign Member'[Responded]==TRUE() &&
            Opportunities[Opportunity Created Date] <= 'Campaign Member'[Campaign Member Association Date]))
 
I would expect the following
 
Some Big Deal 1 RETURN Some Marketing Campaign 4
Some Big Deal 2 RETURN Some Marketing Campaign 3
Some Big Deal 3 RETURN Some Marketing Campaign 3
 
Here is some screen shots of what the dax is returning incorrectly
 
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi vehloaaron ,

     

    Please try the following expression and if you still have problems, I think you can use the EARLIER function for grouping:

     

    Opportunity Create Campaign =
CALCULATE(
    LASTNONBLANK('Campaign Member'[Campaign Name], TRUE()),
    FILTER(
        ALL('Campaign Member'),
        'Campaign Member'[Person ID] = Opportunities[Person ID] &&
        'Campaign Member'[Responded] = TRUE() &&
        Opportunities[Opportunity Created Date] <= 'Campaign Member'[Campaign Member Association Date]
    )
)

     

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

    • vehloaaron's avatar
      vehloaaron
      Frequent Visitor
      2 years ago

      This is still returning the incorrect value, I tried grouping by earlier but that also didn't work.