how to calculate the frequency of Top N of each id in each week

[oakfootballclub]

week		id		value
w1		E01		163
w1		E01		145
w1		E02		94
w1		E02		35
w1		E03		123
w1		E04		8
w1		E06		177
w1		E07		38
w2		E01		103
w2		E02		180
w2		E03		31
w2		E04		57
w2		E06		88
w2		E07		179
w2		E08		69
w3		E02		177
w3		E03		176
w3		E04		62
w3		E06		92
w3		E07		163
w4		E01		160
w4		E02		73
w4		E03		38
w4		E04		47
w4		E06		64
w4		E07		152
w4		E08		129

based on the data above,

notice that the in each week may exist same id with different values,

I want to calculate the frequency of Top 3 value of each week of each id in each, for example,

 in week w1, Top 3 is: E01,E02,E06
in week w1, Top 3 is:E02,E07,E01
in week w1, Top 3 is:E02,E03,E07
in week w1, Top 3 is:E01,E07,E08

so  week top3 frequency of E01 is 3, 

that of E02 is 1,

that of E03 is 2,

that of E04 is 0,

........

do you know how to caluculate it in DAX with high performance? Thank you.

[Anonymous · Accepted solution]

Hi,@aduguid 

thanks for your concern about this issue.
Your answer is excellent!

And I would like to share some additional solutions below.

Hi,@oakfootballclub 

I am glad to help you. 

According to your description, you want to distinct count by max date? 

If I understand you correctly, then you can refer to my solution. 

1. New table, calculate the value grouped according to week and id. 

Table2 = 
ADDCOLUMNS (
    SUMMARIZE (
        Table_,
        Table_[week],
        Table_[id],
        "TotalValue", SUM ( Table_[value] )
    ),
    "TotalValueByWeekAndID", [TotalValue]
)

2.New Column, according to the value calculated in the first step of the descending order, to get the top three values. 

RankWithinWeekAndID = 
RANKX (
    FILTER ( ALL ( 'Table2' ), 'Table2'[week] = EARLIER ( 'Table2'[week] ) ),
    'Table2'[TotalValueByWeekAndID],
    ,
    DESC,
    DENSE
)

3.New Measure, and then use the Measure to filter the visual. 

Top3Frequency = 
VAR CurrentID =
    SELECTEDVALUE ( Table2[id] )
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER ( Table2, Table2[RankWithinWeekAndID] <= 3 && Table2[id] = CurrentID )
        )
    )

4.Here is the result: 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

Try using this calculated column 

RankWithinWeek = 
RANKX(
    FILTER(
        TableName, 
        TableName[week] = EARLIER(TableName[week])
    ), 
    TableName[value], 
    , 
    DESC, 
    DENSE
)

Then you can use this measure 

Top3Frequency = 
VAR CurrentID = SELECTEDVALUE(TableName[id])
RETURN 
CALCULATE(
    COUNTROWS(
        FILTER(
            TableName, 
            TableName[RankWithinWeek] <= 3 && 
            TableName[id] = CurrentID
        )
    )
)