count of values based on rank

[Fiala]

Hi, can somebody help me with this please?

I have a table like this:

Date	Login	N chats	Rank (N chats)
1.1.2020	A	5	1
1.1.2020	B	4	2
1.1.2020	C	3	3
1.1.2020	D	2	4
1.2.2020	A	6	1
1.2.2020	B	5	2
1.2.2020	C	4	3
1.2.2020	D	3	4
1.3.2020	A	9	1
1.3.2020	B	5	2
1.3.2020	C	4	3

Rank funkcion ranks me all values ​​for all days together, not separately for each day.

I need create a matrix with logins and date in rows and with the measure "count logins with rank = 1":

Login	Count  Rank = 1
A	3
B	0
C	0

My attempts so far are failing, it seems like a simple task, but I can't move on. Thank you for all the help.

[Fiala]

@Jihwan_Kim I think I got it, I only slightly adjusted your measure. Thank you very much.

[Fiala]

@Jihwan_Kim Thank you very much. 

In general it works great. But what if I also need to work with the date and see how many times the login "A" was rank 1 in January or last week? The measure seems to ignore the dates and returns the same results every month.

[Jihwan_Kim]

Hi, @Fiala 

Thank you for your feedback.

Sorry that I quite do not understand your question.

In the initial question, the row is showing the date. Do you want to change this to week or month?

Then, simply you can fix the measure to consider the overall calendar.

Or, please share your sample pbix file's link here, together with how your desired outcome looks like.

Then, I can try to look into it to come up with a more accurate measure.

Thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[CNENFRNL]

Calculated column solution,

Measure solution

[Jihwan_Kim · Accepted solution]

Hi, @Fiala 

Please check the below picture and the sample pbix file's link down below.

Rank N chats per day =

IF (

ISFILTERED ( 'Table' ),

RANKX (

ALLEXCEPT ( 'Table', 'Table'[Date] ),

CALCULATE ( SUM ( 'Table'[N chats] ) ),

,

DESC

)

)

Count Rank No.1 =

VAR currentlogin =

MAX ( 'Table'[Login] )

VAR newtable =

FILTER (

SUMMARIZE (

ALL ( 'Table' ),

'Table'[Date],

'Table'[Login],

"@rank", [Rank N chats per day]

),

[@rank] = 1

)

RETURN

IF(

ISFILTERED('Table'[Login]),

COALESCE ( COUNTROWS ( FILTER ( newtable, 'Table'[Login] = currentlogin ) ), 0 ))

https://www.dropbox.com/s/6nob3azdoorb6qf/fiala.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM