How to retrieve the highest ranked value from another table

[ksfx77]

Hi all,

Following on from an earlier query, I need to understand how to return a value from another table based on its rank. These are text based values, and there are about 10 possible different values.

BACKGROUND
I have a bunch of session data in Tabe 1, and I have a bunch of user data in Table 2. A user will have typically have played many sessions, hence there will be many rows per user in the session table.

I am trying to add a column to table 2, based on a look up of data in table 1, which I can do fine by matching id's on both tables. However I need to retrieve the highest ranked value for each user in Table 1.

Thanks in advance

Kevin

[vanessafvg]

what is the value of the highest rank based on the most sessions?  please can you post some sample data

[ksfx77]

Hi vanessafvg,

It would be based on most occurrences.  So in the very simple example below 'Platform 2' and 'Platform 3' would have a count of one, and 'Platform 1' has a count of 2, so the text 'Platform 1' would be returned.

TABLE 1

date, platform, uniqueId 
10/03/2021, platform 1, id1 
10/03/2021, platform 2, id1 
10/03/2021, platform1, id1 
10/03/2021, platform 3, id1 

I hope this helps?

Kevin

[vanessafvg]

it does help but you have different id per platform is that what you intended?  because then platform 1 would be returned to table a at the user level, but that user didn't watch it 2 x

[ksfx77]

Hi vanessafvg,

Any guidance you can give will be greatly appreciated.

Thanks!

[vanessafvg]

sorry i have been quite busy

give this a bash.   hard to know what issues might occur without actual data

create a calculated column on the user table

TOP Session =

var cnt = CALCULATE(COUNTROWS(Sessions))

var rnk = CALCULATE(RANKX(all(Sessions),cnt))

var top1 = CALCULATE(FIRSTNONBLANK(Sessions[Platform], rnk = 1))

return top1

[ksfx77]

Thanks - I really appreciate your time 🙂

The ranking of platform would need to be done per user, so I would need to use a filter to add table1.uid = table2=uid, right?

[vanessafvg]

sorry ive rejigged it a bit, hope this works

new measures 

# rows = CALCULATE(COUNTROWS(Sessions))

rank = CALCULATE(RANKX(ALLEXCEPT(Sessions,Sessions[ID]),[# rows]))

new column in user table

TOP Session = CALCULATE (

LASTNONBLANK (Sessions[Platform], 1 ),

FILTER ( TOPN ( 1, VALUES (Sessions), [rank], asc ), TRUE () )

)

[ksfx77]

Hi - I am afraid this is not working.  In one case I checked it returned a platform a user had not used.

Just to be clear on what I am trying to do. 

1. For each user in the user table look up all sessions they have played (in the sessions table) using the UID
2. Of the rows returned count each platform (column in sessions table) used (eg. windows=10, android=8, firetv=2)
3. Rank these in order and return the most used platform as a label - eg. windows
4. Store this in a new column in the users table

Thanks so much 🙂

[vanessafvg]

I am afraid you going to need to provide data, its almost impossible to understand all the scenarios in the data without understanding what is in it. what you have provided previously is not sufficient.  I can't test properly without decent sample data.    I am not having the same issues as you with the small set of sample data.  Please provide both tables in the sample set.

If you can provide a pbix, with a decent amount of sample data and with the way you have set up the measures i have created that would be best so I can understand how you are implementing them.

[ksfx77]

Hi,

Where shall I send this?  I cannot see an upload file option?

[vanessafvg]

you will need to use dropbox, one drive or similar and share it

[ksfx77]

Here you go - https://www.dropbox.com/s/xi4w9l3bcwuw3ri/sample_platformrank.pbix?dl=0

[vanessafvg]

oops my bad 🙂  

i should have said create a measure for the last calc not a column

see attached.  

[ksfx77]

I implemented this and when I try to display the data I get...

Also, I really need it available as a column, with the ability to use it as a filter.

Any ideas?

[vanessafvg]

when you say you implemented this what does that mean?  the visuals works fine in the model i have.  What visual have you placed there?  You need to give more details - its also best to mention all your requirements at the beginning of your request as that has an impact on the solution.  

Please explain how you plan to use this filter?    Please see what i have attached that has thrown no memory issue for me

[ksfx77]

Good spot!  No that was not intended.  Now corrected.