how to calculate frequency use DAX

[oakfootballclub]

I have the following data:

table: fact_table

year		week		id		value
2023		w1		E01		163
2023		w1		E02		35
2023		w1		E03		123
2023		w1		E04		8
2023		w1		E06		177
2023		w1		E07		38
2023		w2		E01		103
2023		w2		E02		180
2023		w2		E03		31
2023		w2		E04		57
2023		w2		E06		88
2023		w2		E07		179
2023		w2		E08		69
2023		w3		E02		177
2023		w3		E03		176
2023		w3		E04		62
2023		w3		E06		92
2023		w3		E07		163
2023		w4		E01		160
2023		w4		E02		73
2023		w4		E03		38
2023		w4		E04		47
2023		w4		E06		64
2023		w4		E07		152
2023		w4		E08		129
2024		w1		E02		63
2024		w1		E03		111
2024		w1		E04		44
2024		w1		E06		45
2024		w1		E07		175
2024		w2		E01		75
2024		w2		E02		154
2024		w2		E03		60
2024		w2		E04		21
2024		w2		E06		57
2024		w2		E07		153
2024		w2		E08		175
2024		w3		E02		94
2024		w3		E03		172
2024		w3		E04		75
2024		w3		E06		101
2024		w3		E07		99
2024		w4		E01		145

and table id:

id

E01

E02

...

E10

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

in year 2023, in week w1, Top 3 is: E06,E01,E03
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.

[rajendraongole1 · Accepted solution]

Hi @oakfootballclub -Create a measure to rank the values within each week.

RankValue =
RANKX(
FILTER(
'fact_table',
'fact_table'[year] = EARLIER('fact_table'[year]) &&
'fact_table'[week] = EARLIER('fact_table'[week])
),
'fact_table'[value],
,
DESC,
DENSE
)

create a calculated table to store the top 3 values for each week using following new table.

Top3Values =

FILTER(
'fact_table',
'fact_table'[RankValue] <= 3
)

Create a measure to count the frequency of each ID appearing in the top 3

Top3Frequency =
CALCULATE(
COUNTROWS(Top3Values),
ALLEXCEPT('fact_table', 'fact_table'[id])
)

Hope it works, please check. 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[oakfootballclub]

Thank you very much, earlier function in my power bi did not work, it says paramater is not the correct type. do you know why?