how to write frequency DAX with high performance

[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.

[Irwan · Accepted solution]

Hello @oakfootballclub 

please check if this accomodate your need (not sure if this DAX with high performace).

1. Create rank or index based on 'value' column in fact_table so you can easily find top 3.

Rank =

var _Year = 'fact_table'[year]

var _Week = 'fact_table'[week]

Return

RANKX(

    FILTER(

        'fact_table',

        'fact_table'[year]=_Year&&

        'fact_table'[week]=_Week

    ),

    'fact_table'[value],,

    DESC

)

2. Create new table with summarize

Year =

var _t1 = SUMMARIZE('fact_table','fact_table'[year])

var _t2 = SUMMARIZE('fact_table','fact_table'[week])

var _rank = SELECTCOLUMNS({(1),(2),(3)},"Rank",[Value])

Return

GENERATE(

    _t1,

    GENERATE(

        _t2,_rank

    )

)

_rank is used for creating rank number 1, 2, and 3 since you want to see top 3 (if you need more such as top 4, you can add the value).

3. in summarize table, create calculated column for calculating top 3 ID and value based on exact same year, exact same week, and exact same rank.

ID =

MAXX(

    FILTER(

        'fact_table',

        'fact_table'[year]='Year'[year]&&

        'fact_table'[week]='Year'[week]&&

        'fact_table'[Rank]='Year'[Rank]

    ),

    'fact_table'[id]

)

Value =

MAXX(

    FILTER(

        'fact_table',

        'fact_table'[year]='Year'[year]&&

        'fact_table'[week]='Year'[week]&&

        'fact_table'[Rank]='Year'[Rank]

    ),

    'fact_table'[value]

)

4. in table ID, create measure to count frequency of top 3 ID

Count =

var _ID = SELECTEDVALUE('ID'[ID])

Return

CALCULATE(COUNTROWS(RELATEDTABLE('Year')),'Year'[ID]=_ID)

5. Create year and week slicer (value in slicer should be taken from summarized table since no table-relationship made)

6. Create Table visual contain of year, week, ID, and Value (all value in table should be taken from summarized table since no table-relationship made)

7. Create Table visual contain of ID and Count which is previously measured. In this table, go to Format -> "Edit Interaction" then remove relationship between this table and week slicer (based on your explaination, I assumed you only want to see top 3 frequency ID in annual stage). By removing this interaction, this table will not get filtered when selecting week slicer.

Hope this will help you.

Thank you.