Forum Discussion
Function returning blank in DAX
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:
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() )
15 Replies
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.
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
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() )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.
