SUMX formula to filter on Measure that provides text - not working

[guanel1]

I created a DAX as below that gives me the Current fiscal year - which is "FY2023". 

Current FY = MAX('Table 1'[FY])

This is a text field. 

I then want to filter the Billed Hours to only show the hours from this Fiscal Year. I created this DAX: 

Hours Billed = SUMX(FILTER('Table 1','Table 1'[FY]=[Current FY]),'Table 1'[Hours])

However, this is not working. It is not filtering out any of the hours. 

When I substitute the measure [Current FY] above with "FY2023" then it works.

Hours Billed = SUMX(FILTER('Table 1','Table 1'[FY]="FY2023"),'Table 1'[Hours])

This makes me think that the text is having issues - but the text that it is looking for comes from the same table so I'm not sure what is the issue.

I cannot just put "FY2023" in the formula because I need to keep the result dynamic over time. When new data is added, the hours billed sum updates to the new FY data. 

Thank you. 

[tamerj1]

Hi @guanel1 

Both [Current FY] and [Hours Billed] are measures right? How does your visual look like? Which columns / from which tables are involved?

[guanel1]

Here is the example data to help explain my issue.  

Here are the measures i have created. 

Here is what happens in PBI. I want to take the hours billed and forecast hours for the current year (this needs to stay dynamic so that this report can be used each year). 

The MEASURE NOT WORKING is where i tried to used the measures as listed above. The other table is where I replaced the measure [Current FY] with "FY2023" - and then it works. 

[tamerj1]

@guanel1 
Please paste the same data as text (not screenshot).

[guanel1]

I have found that adding the measure for [Current FY] works when I add it to the measure for table 2 but not for table 1. It seems like the data only works when you pull from a different table, but not when the data is from the same table...

Table 1 = Hours Billed

FY	Qtr	Project	Hours
FY2022	1	A	250
FY2022	2	A	500
FY2022	3	A	0
FY2022	4	A	0
FY2023	1	A	0
FY2023	2	A	300
FY2023	3	A	345
FY2023	4	A	525
FY2022	1	B	0
FY2022	2	B	150
FY2022	3	B	400
FY2022	4	B	75
FY2023	1	B	250
FY2023	2	B	0
FY2023	3	B	175
FY2023	4	B	350

Table 2 = Hours Forecasted

FY	Qtr	Project	Hours
FY2022	1	A	300
FY2022	2	A	300
FY2022	3	A	300
FY2022	4	A	300
FY2023	1	A	250
FY2023	2	A	500
FY2023	3	A	500
FY2023	4	A	1250
FY2022	1	B	0
FY2022	2	B	150
FY2022	3	B	150
FY2022	4	B	150
FY2023	1	B	150
FY2023	2	B	0
FY2023	3	B	250
FY2023	4	B	250

Measures Created: 

Current FY = MAX('Table 1'[FY])

Hours Billed = SUMX(FILTER('Table 1', 'Table 1'[FY] = [Current FY]))

Hours Forecast = SUMX(FILTER('Table 2', 'Table 2'[FY] = [Current FY]))

[guanel1]

So, looking at it more: 

I looks like the [Current FY] measure works only when it is coming from the OTHER table. 

The col "Hours Forecast" is correct when i use the [Current FY] in the measure. But when i try to use the [Current FY] that is coming from the same table, it doesnt work. 

How do i fix this issue? 

[tamerj1 · Accepted solution]

@guanel1 
Please use 

Current FY = CALCULATE ( MAX ( 'Table 1'[FY] ), REMOVEFILTERS ( ) )

[tamerj1]

@guanel1 
Have you tried this?

[guanel1]

Yes, I tested it and it worked. Thank you!

Can you explain the logic behind adding the REMOVEFILTERS to the [Current FY] Measure? 

[tamerj1]

@guanel1 
It is context transition. In your measure:

Hours Billed = SUMX(FILTER('Table 1', 'Table 1'[FY] = [Current FY]))

Hours Forecast = SUMX(FILTER('Table 2', 'Table 2'[FY] = [Current FY]))

The measure [Current FY] is placed inside FILTER which is an iterator function. That would force context transition converting the Row context into a filter context. The screenshot below shows how both [Current FY] & [Current FY REMOVEFILTERS] measures are being evaluated inside FILTER. I hope once you can spot the difference, everything will become clear and you can can understand why you were getting those numbers.

[guanel1]

Thankyou, i apreciate your help and explanations.