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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.