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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.


Follow on LinkedIn
@ 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...

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.


Follow on LinkedIn
@ 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...

@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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors