topic Re: SUMX formula to filter on Measure that provides text - not working in DAX Commands and Tips

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

guanel1 — Mon, 27 Feb 2023 02:23:31 GMT

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.

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

tamerj1 — Mon, 27 Feb 2023 02:55:28 GMT

Hi guanel1

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

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

guanel1 — Mon, 27 Feb 2023 15:51:06 GMT

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.

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

guanel1 — Mon, 27 Feb 2023 16:07:04 GMT

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?

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

tamerj1 — Mon, 27 Feb 2023 16:24:44 GMT

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

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

tamerj1 — Mon, 27 Feb 2023 17:15:36 GMT

guanel1
Please use

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

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

guanel1 — Mon, 27 Feb 2023 17:18:17 GMT

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

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

tamerj1 — Mon, 27 Feb 2023 18:28:45 GMT

guanel1
Have you tried this?

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

guanel1 — Mon, 27 Feb 2023 23:03:50 GMT

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

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

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

tamerj1 — Tue, 28 Feb 2023 05:31:27 GMT

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.

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

guanel1 — Wed, 05 Apr 2023 14:26:06 GMT

Thankyou, i apreciate your help and explanations.