Function returning blank in DAX

[Anonymous]

testing = 
VAR extractCampaignYear = IF (ISFILTERED('Campaign Overview'[campaign]),VALUE(MIN('Campaign Overview'[previousExtractedYear])),0)

RETURN

SUMX(
FILTER(
    SUMMARIZE (
    'Campaign Overview',
    'Campaign Overview'[campaign],
    "extractedCampaign",TRIM(MIN('Campaign Overview'[extractedCampaignName])),
    "campaignYear", VALUE(TRIM(MIN('Campaign Overview'[extractedYearFromCampaign]))),
    "Sales", SUM ( 'Campaign Overview'[sales] )
),
[campaignYear] = extractCampaignYear), [Sales])

I have written a DAX code that checks whether a campaign has been filtered and if it has it basically gets the previous campaign year and then the function should return the total sales for the previous year campaign but its just returning blank.

However, when I remove the extractCampaignYear and make it [campaignYear] = 2022 for example, it works.

Does anyone know why the campaignYear and extractCampaignYear are not matching up when they are both stored as integers as I'm using the value function so I dont know why its returning blank.
When using a variable:

When I explicitly type 2022:

[AlexisOlson]

I'm not seeing any unexpected behavior.

matches

Using 2022 is different when no campaign is selected because extractCampaignYear variable is 0 in this case.

[Anonymous · Accepted solution]

I actually managed to solve it, its because when I filter something with my slicer it also filters the summarized table so I needed to add removefilters() within my DAX code like so:

testing = 
VAR extractCampaignYear = IF (ISFILTERED('Campaign Overview'[campaign]),MIN('Campaign Overview'[previousExtractedYear]),0)

RETURN
CALCULATE(
SUMX(
FILTER(
SUMMARIZE (
'Campaign Overview',
'Campaign Overview'[campaign],
"extractedCampaign",TRIM(MIN('Campaign Overview'[extractedCampaignName])),
"campaignYear", MIN('Campaign Overview'[extractedYearFromCampaign]),
"Sales", SUM ( 'Campaign Overview'[sales] )
),
[campaignYear] = extractCampaignYear), [Sales]),
REMOVEFILTERS()
)

[AlexisOlson]

Have you tested that your extractCampaignYear variable is returning a year rather than 0?

Also, if your year columns are integers, then you shouldn't need to use TRIM or VALUE to work with them.

[Anonymous]

Yes I tested it already and it returns the previous year.

[AlexisOlson]

Interesting. I can't think of a reason for this behavior unless it is data type related.

Can you try it without the extra TRIM and VALUE after double-checking that 'Campaign Overview'[previousExtractedYear] and Campaign Overview'[extractedYearFromCampaign] are both the same data type (whole number)?

testing =
VAR extractCampaignYear =
    IF (
        ISFILTERED ( 'Campaign Overview'[campaign] ),
        MIN ( 'Campaign Overview'[previousExtractedYear] ),
        0
    )
VAR _sum =
    SUMX (
        FILTER (
            SUMMARIZE (
                'Campaign Overview',
                'Campaign Overview'[campaign],
                "extractedCampaign", TRIM ( MIN ( 'Campaign Overview'[extractedCampaignName] ) ),
                "campaignYear", MIN ( 'Campaign Overview'[extractedYearFromCampaign] ),
                "Sales", SUM ( 'Campaign Overview'[sales] )
            ),
            [campaignYear] = extractCampaignYear
        ),
        [Sales]
    )
RETURN
    extractCampaignYear

[Anonymous]

I tried your code and it returns the correct previous year the same way mine does. 

So I dont understand why it doesnt work when adding it as a filter to say the campaign year = extractCampaignYear

[AlexisOlson]

Whoops. I meant to put _sum after RETURN instead of the first variable to see if removing the TRIM and VALUE did anything.

testing =
VAR extractCampaignYear =
    IF (
        ISFILTERED ( 'Campaign Overview'[campaign] ),
        MIN ( 'Campaign Overview'[previousExtractedYear] ),
        0
    )
VAR _sum =
    SUMX (
        FILTER (
            SUMMARIZE (
                'Campaign Overview',
                'Campaign Overview'[campaign],
                "extractedCampaign", TRIM ( MIN ( 'Campaign Overview'[extractedCampaignName] ) ),
                "campaignYear", MIN ( 'Campaign Overview'[extractedYearFromCampaign] ),
                "Sales", SUM ( 'Campaign Overview'[sales] )
            ),
            [campaignYear] = extractCampaignYear
        ),
        [Sales]
    )
RETURN
    _sum

[Anonymous]

It also returns blank, I also double checked and they are both stored as whole numbers

[AlexisOlson]

If you have a file you can share that reproduces this behavior, I'll take a look. Otherwise, I'm out of ideas for now.

[Anonymous]

how shall I share the pbix with you?

[AlexisOlson]

A link to the file publically shared online via OneDrive/SharePoint/Dropbox or a similar cloud service. Be sure it contains no sensitive data.

[Anonymous]

https://drive.google.com/file/d/1pn3tK09NnPxalh-iYhIH6oqEiO8aDs3t/view?usp=drive_link

The data has been sanitised so doesn't contain sensitive information

[AlexisOlson]

Great. Now set the permissions so that others can see it.

Currently:

[Anonymous]

Its done

[Anonymous]

I will upload the pbix file for you to take a look