cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasDay
Impactful Individual
Impactful Individual

RANKX with SUMMARIZE AND ADDCOLUMNS

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.

TrendsTableTemp.png

 

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

 

 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

ThomasDay
Impactful Individual
Impactful Individual

@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
TrendsTableRANKX.png

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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@ThomasDay , Try changes in return

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
ThomasDay
Impactful Individual
Impactful Individual

@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

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

 

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

ThomasDay
Impactful Individual
Impactful Individual

@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
TrendsTableRANKX.png

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.