DAX: Issues with not finding column name that is clearly there.

[ssandstrom]

What I am trying to do is get a sum of measure CntVariance (count difference from currentmonth-previousmonth) by a group of existing columns so I can generate SumCntVariance (sum within the group) and eventually work my way to an average count variance (AvgCntVariance) within that same group.  The catch is I would really not like to include November because its the initial run, so the differences in cnts should not be used as its the initial load of data.

Here an example of some data:

BuildPhase	MetricDescription	CntVariable	ReleaseMonthYear	Month_YY.Year	Month_YY.Month	Cnt	PreviousMonthCnt	CntVariance
1	A	Records	202211	2022	November	2218267		2218267
1	A	Records	202212	2022	December	2400358	2218267	182091
1	A	Records	202301	2023	January	2408373	2400358	8015
1	A	Records	202302	2023	February	2419882	2408373	11509
1	A	Records	202303	2023	March	2457902	2419882	38020
1	A	Records	202304	2023	April	2471238	2457902	13336
1	B	Male	202211	2022	November	3502		3502
1	B	Male	202212	2022	December	2975	3502	-527
1	B	Male	202301	2023	January	2610	2975	-365
1	B	Male	202302	2023	February	4317	2610	1707
1	B	Male	202303	2023	March	4570	4317	253
1	B	Male	202304	2023	April	4591	4570	21
1	B	Female	202211	2022	November	3402		3502
1	B	Female	202212	2022	December	2777	3502	-527
1	B	Female	202301	2023	January	2929	2975	-365
1	B	Female	202302	2023	February	5002	2610	1707
1	B	Female	202303	2023	March	4243	4317	253
1	B	Female	202304	2023	April	4520	4570	21
1	C	MemberID	202211	2022	November	18237		18237
1	C	MemberID	202212	2022	December	31011	18237	12774
1	C	MemberID	202301	2023	January	8014	31011	-22997
1	C	MemberID	202302	2023	February	11509	8014	3495
1	C	MemberID	202303	2023	March	9990	11509	-1519
1	C	MemberID	202304	2023	April	13336	9990	3346

My MetricDescription is a QA metric, CntVariable is can be things like , Distinct MemberID, Records, "Y"/"N", "Male"/"Female" ect. I have added a date table that maps the release calle ColumnStackDates where ReleaseMonthYear can be tied to date fields

Releationship is that ReleaseMonthYear=ReleaseCycle in the table below.  I am including this in the question because it may be relevant to reason it may not be working:

Relationship set up:

I tried making this a one to one but it freaked out at me an told me that the relationship was invalid.

So I stuck with the one to many and moved on trying to get my groupings and my SumofCntVariance a few difference ways:

I initially tried this equation:

SumofCnt = SUMX(

FILTER(

ALLSELECTED('metadata MetricsTable'[BuildPhase], 'metadata MetricsTable'[MetricDescription], 'metadata MetricsTable'[CntVariable]),

NOT(ISINSCOPE('ColumnStackDates'[Month_YY]) && MONTH('ColumnStackDates'[Month_YY]) = 11) ),

CALCULATE

( [CntVariance],

     ALLSELECTED('metadata MetricsTable'[BuildPhase], 'metadata MetricsTable'[MetricDescription], 'metadata MetricsTable'[CntVariable]),

     NOT(ISINSCOPE('ColumnStackDates'[Month_YY]) && MONTH('ColumnStackDates'[Month_YY]) = 11) ) )

DAX freaks out saying it could not find [Month_YY], but only in that one area in red above, everywhere else I use [Month_YY] is kosher.

In trying a work around I decided to just use ReleaseCycleMonth

SumofCnt = SUMX(

FILTER(

ALLSELECTED('metadata MetricsTable'[BuildPhase], 'metadata MetricsTable'[MetricDescription], 'metadata MetricsTable'[CntVariable]),

NOT(ISINSCOPE('metadata MetricsTable'[ReleaseMonthYear]) && 'metadata MetricsTable'[ReleaseMonthYear] = "202211")

    ),

    CALCULATE(

        [CntVariance],

ALLSELECTED('metadata MetricsTable'[BuildPhase], 'metadata MetricsTable'[MetricDescription], 'metadata MetricsTable'[CntVariable]),

       NOT(ISINSCOPE('metadata MetricsTable'[ReleaseMonthYear]) && 'metadata MetricsTable'[ReleaseMonthYear] = "202211")

    )

)

I GET THE SAME ERROR IN THE SAME LOCATION.  

I have tried to get help from chatgpt, but it keepts telling me I spelt things wrong then says try this: "<insert same exact equation I am using>  

I am going mad.  Can someone explain to my why this wont work?

[Greg_Deckler]

@ssandstrom In a measure, if you reference a column directly you need to wrap it in an aggregation like MAX, MIN, SUM, etc.