Search code examples
variablespowerbidaxpowerbi-desktopmeasure

Function returning BLANK in DAX

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.

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

RETURN

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

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 using a variable:

enter image description here

When I explicitly type 2022: enter image description here

Solution

  • Try this

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

    When you select a value from the slicer, you're filtering your table and hence 2023 is filter so when you then look for 2022, you get zero records.

    FYI, I haven't fixed your summarise code but you should NOT create new columns using summarise - use ADDCOLUMNS() instead to avoid weird issues that will be impossible to debug.