LASTNONBLANK Help

[vehloaaron]

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

[Anonymous]

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]

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