RANKX measure returning all 1's

[christinamarie]

I'm currently using the following three tables: 

1. Stock Data - a list of stocks, the week they were purchased, a boolean field to indicate whether they have been sold, and if true, the week they were sold

2. Stock Pictures - a list of all 16 different stocks and a URL of a corresponding image of that stock

3. Week Table - a table with a single column (weeks listed 1-5)

I have written the following measure to calculate the number of stocks sold, filterable by the week they were sold:

Sold Stock = CALCULATE(COUNT('Stock Data'[Share]), 'Stock Data'[Is_Sold] = TRUE(), USERELATIONSHIP('Stock Data'[Week Sold], 'Week Table'[Week Number]))

And I wrote the following rank measure to calculate the rank each share is for stocks sold, and it dynamically changes when different weeks from a 'Week Table' [Week Number] slicer is selected:

Sold Stock Rank = RANKX(ALLSELECTED('Stock Data'[Share]), CALCULATE(COUNT('Stock Data'[Share]), 'Stock Data'[Is_Sold] = TRUE(), USERELATIONSHIP('Stock Data'[Week Sold], 'Week Table'[Week Number])))

I have an ImageViewer visual where I want to put a picture of the stock with the most shares sold (Sold Stock Rank = 1) that changes appropriately when a different week is selected. It hasn't been working the way I had intended, so I made a table to test out the relationships between all the variables. 

There is currently an active relationship between 'Stock Data' [Share] and 'Stock Pictures' [Share Name], an active relationship between 'Stock Data' [Week] and 'Week Table' [Week Number] and an inactive relationship between 'Stock Data' [Week Sold] and 'Week Table' [Week Number]. I created a table visual with 'Stock Data'[Share], 'Stock Pictures'[Image URL] and the Sold Stock Rank measure. All the ranks are equal to 1, and it looks like 'Stock Data' and 'Image URL' became un-joined, even though there's still an active relationship. Any help would be super appreciated!

[v-frfei-msft · Accepted solution]

Hi @christinamarie,

Based on my test, we can update your formulas as below to achieve your goal.

Sold Stock = CALCULATE(COUNT('Stock Data'[Share]), 'Stock Data'[Is_Sold] = TRUE(), USERELATIONSHIP('Stock Data'[Week Sold], 'Week Table'[Week Number]))

Sold Stock Rank = IF(ISBLANK([Sold Stock]),BLANK(),RANKX(ALL('Stock Data'[Share]),[Sold Stock],,DESC,Dense))

For more details, please check the pbix as attached.

https://www.dropbox.com/s/m5b9sdpp4i1zoyx/RANKX%20measure%20returning%20all%201%27s.pbix?dl=0 

Regards,

Frank

[v-frfei-msft]

Hi @christinamarie,

Does that make sense? If so, kindly mark my answer as a solution to close the case please.

Regards,

Frank