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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gjohnson900
Helper I
Helper I

RANKX always returns 1 or total number of rows. PLEASE help

Please help!

 

Seriously, I've tried every permutation of measure/column.  I can see the finish line, but I can't get over it.

 

Here's my table of data.  You can clearly see the CustCount measure is working...

 

rankx_table.JPG

 

So, why on earth wouldn't either of my RANKX attempts work?  What's so hard about ranking a measure that's working?

 

dRanker is a calculated column...

dRanker = RANKX(ALL(FW17), [CustCount])

dRanker2 is a calculated measure...

dRanker2 = RANKX(ALLSELECTED(FW17), CALCULATE(DISTINCTCOUNT(FW17[customerkey])))

CustCount is a calculated measure as well...

CustCount = DISTINCTCOUNT(FW17[customerkey])

Pulling my hair out here... how on earth is this difficult?

 

I've checked the other solutions on this forum for the exact title of mine, and they all don't work.

 

Just for grins, here's my model...

 

model.JPG

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

Hi gjohnson900,

 

Modify your measure CustCount like pattern below:

 

CustCount = Calculate(DISTINCTCOUNT(FW17[customerkey]), ALLEXCEPT(FW17, FW17[source]))

 And check if it can meet your requirement.

 

Regards,

Jimmy Tao

 

View solution in original post

5 REPLIES 5
v-yuta-msft
Community Support
Community Support

Hi gjohnson900,

 

Modify your measure CustCount like pattern below:

 

CustCount = Calculate(DISTINCTCOUNT(FW17[customerkey]), ALLEXCEPT(FW17, FW17[source]))

 And check if it can meet your requirement.

 

Regards,

Jimmy Tao

 

I am having issue with the same thing as mentioned below. I am doing a distinct count on the number of sales orders and how long it took to fill the order. I am confused on what the "source" would be as mentioned below

That allowed me to rank just the [source], yes.  Thanks Jimmy.  I'm closer than I've been able to get by implementing this, but I still have some outstanding issues that I'll bring up in another thread.  

 

Thanks.

gjohnson900
Helper I
Helper I

Here's the gist gang...

 

Instead ofthe dRanker2 DAX that I was using...

dRanker2 = RANKX(ALLSELECTED(FW17), CALCULATE(DISTINCTCOUNT(FW17[customerkey])))

I had to specify the column that I wanted ranked...

dRanker2 = RANKX(ALLSELECTED(FW17[source]), CALCULATE(DISTINCTCOUNT(FW17[customerkey])))

In that regard, it's a lot like the PARTITION clause when using RANK() in tSQL.  

 

 

Greg_Deckler
Super User
Super User

I am guessing that it is the context in which your measure is evaluating within the column that is the problem. You might try wrapping that measure in a CALCULATE with a RELATED or RELATEDTABLE or ALL filter of some sort. Really difficult to say without sample/example raw data. 

 

In other words, your measure is calculating fine within the table visualization, but that is within a certain context. When you evaluate that same measure in a table as part of a calculated column, then it is most probably an entirely different context. Often, what I do is to replicate the context of my table visualization by creating a temp table using FILTER or SUMMARIZE, etc. Then I use ADDCOLUMNS to add in my measure. Then I can can use another ADDCOLUMNS to add in my RANKX calculation to that temporary table. Then I just have to filter down the temp table to the row that I want for my RETURN (or use an "X" aggregation function like SUMX, MAXX, 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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