Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Function returning blank in DAX

testing = VAR extractCampaignYear = IF (ISFILTERED('Campaign Overview'[campaign]),VALUE(MIN('Campaign Overview'[previousExtractedYear])),0) RETURN SUMX( FILTER( SUMMARIZE ( 'Campaign Over...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
AlexisOlson
Icon for Super User rankSuper User
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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

 

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        2 years ago

        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