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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

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