RANKX with SUMMARIZE AND ADDCOLUMNS

[ThomasDay]

Hello fellow DAXers,

I am having a difficult time getting a Rankx column added to a memory table created by SUMMARIZE

At the end of the day using the snippet at bottom of post, I get the error message
"Function 'RANKX' does not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."  

Here's the what the TrendsTableTemp looks like referenced in the code snippet: (I put that code into create table so I know it works) And all I want is another column with a Ranking of the dates in descending order using the DataMonthID (or the Measure End Date for that matter)--1,2,3,4,5,6.

Any idea what's happening here?

Thank you , Tom

Here's the code snippet...

BuildTrendsTable = 
//Housekeeping to feed the table to debug
						VAR VariableName = "ADC"
						VAR ProviderNoNewtonWell = 220101
						VAR LastTrendDate = CALCULATE(LASTDATE(Tr_MeasureTrends[Measure End Date]),
                            Tr_MeasureTrends[Variable_Name] = VariableName)
						VAR LastMonthID = CALCULATE(MAX(DateTable[MonthID]), DateTable[Date] = LastTrendDate)
						VAR LastValue = CALCULATE(MAX(Tr_MeasureTrends[Variable_Value]),
                            Tr_MeasureTrends[Measure End Date]= LastTrendDate,
						    Tr_MeasureTrends[Variable_Name] = VariableName)
						VAR Direction = CALCULATE(MIN(HospVariables[Direction(1/0/-1)]),HospVariables[Variable] = VariableName)
						RETURN
//CREATE the memory table TrendsTableTemp using SUMMARIZE AND ADDCOLUMNS TABLE                        
                        VAR TrendsTableTemp = ADDCOLUMNS(
						SUMMARIZE( FILTER(Tr_MeasureTrends,Tr_MeasureTrends[Variable_Name] = VariableName 
						&& [ProvdrNo] = ProviderNoNewtonWell), 
						Tr_MeasureTrends[Measure End Date],
						Tr_MeasureTrends[ProvdrNo],
						"MetricValue" , AVERAGE(Tr_MeasureTrends[Variable_Value])),
						"DataMonthID",LOOKUPVALUE(DateTable[MonthID],DateTable[Date],Tr_MeasureTrends[Measure End Date]))
//TRY TO ADD A RANKX COLUMN                        
                        RETURN
						ADDCOLUMNS(TrendsTableTemp,"MonthRank",						
						RANKX (
						TrendsTableTemp,
						EARLIER ([MetricValue]) = [MetricValue]
							,
							[DataMonthID],
						,
						DENSE
						))

[Greg_Deckler · Accepted solution]

@ThomasDay - Pretty sure this is where your problem lies (bold)

ADDCOLUMNS(TrendsTableTemp,"MonthRank",
RANKX (
TrendsTableTemp,
EARLIER ([MetricValue]) = [MetricValue]
,
[DataMonthID],
,
DENSE
))

But hard to tell exactly without testing. You may find this useful, https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@ThomasDay , Try changes in return

ADDCOLUMNS(TrendsTableTemp,"MonthRank",
RANKX (
filter(all(TrendsTableTemp),
EARLIER ([MetricValue]) = [MetricValue])
[DataMonthID],,
,Desc,
DENSE))

[ThomasDay]

@amitchandak 
Thank you for your help--and without the benefit of any data to use for testing.  I appreciate the time you took.  FYI, It resulted in the error:
"The ALL function expects a table reference for argument '1', but a table expression was used."
--I've been having that trouble when using Memory Tables with Filters and Filter Functions.  I did post the snippet that worked above if you're interested.

Thank you again,

Tom

[AntrikshSharma]

@ThomasDay  Glad you found a solution, also please mark @Greg_Deckler  & your solution as solved.

[Greg_Deckler · Accepted solution]

@ThomasDay - Pretty sure this is where your problem lies (bold)

ADDCOLUMNS(TrendsTableTemp,"MonthRank",
RANKX (
TrendsTableTemp,
EARLIER ([MetricValue]) = [MetricValue]
,
[DataMonthID],
,
DENSE
))

But hard to tell exactly without testing. You may find this useful, https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[ThomasDay · Accepted solution]

@Greg_Deckler
Yes, my apologies for not putting up testing tables and more context.  That said, you led me right to the answer with the link!  Once I eliminated the EARLIER function, it all became easier.  It ends up looking like the following:

ADDCOLUMNS(TrendsTableTemp,"MonthRank",                       

                        RANKX (

                        TrendsTableTemp,

                            [DataMonthID],

,

DESC,

Dense

Since the sort field isn't a calculation, I can use the value of the "expression" as the "value" (no need for EARLIER and can omit value).
Thank you very much!

Tom