how to write frequency DAX with high performance

oakfootballclub — Wed, 03 Jul 2024 02:49:56 GMT

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:

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.

Re: how to write frequency DAX with high performance

Irwan — Wed, 03 Jul 2024 05:47:17 GMT

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.

topic how to write frequency DAX with high performance in DAX Commands and Tips

how to write frequency DAX with high performance

Re: how to write frequency DAX with high performance