Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ssandstrom
Regular Visitor

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

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:

 

BuildPhaseMetricDescriptionCntVariableReleaseMonthYearMonth_YY.YearMonth_YY.MonthCntPreviousMonthCntCntVariance
1ARecords2022112022November2218267 2218267
1ARecords2022122022December24003582218267182091
1ARecords2023012023January240837324003588015
1ARecords2023022023February2419882240837311509
1ARecords2023032023March2457902241988238020
1ARecords2023042023April2471238245790213336
1BMale2022112022November3502 3502
1BMale2022122022December29753502-527
1BMale2023012023January26102975-365
1BMale2023022023February431726101707
1BMale2023032023March45704317253
1BMale2023042023April4591457021
1BFemale2022112022November3402 3502
1BFemale2022122022December27773502-527
1BFemale2023012023January29292975-365
1BFemale2023022023February500226101707
1BFemale2023032023March42434317253
1BFemale2023042023April4520457021
1CMemberID2022112022November18237 18237
1CMemberID2022122022December310111823712774
1CMemberID2023012023January801431011-22997
1CMemberID2023022023February1150980143495
1CMemberID2023032023March999011509-1519
1CMemberID2023042023April1333699903346

 

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:

 

ssandstrom_1-1681943161730.png

 

Relationship set up:

ssandstrom_2-1681944383266.png

 

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

ssandstrom_3-1681944447764.png

 

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.

ssandstrom_4-1681944811577.png

 

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.  
 
ssandstrom_5-1681945055219.png
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?

 

1 REPLY 1
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.