Forum Discussion
oakfootballclub
Helper IV
Helper IVhow to write frequency DAX with high performance
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.
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]ReturnRANKX(FILTER('fact_table','fact_table'[year]=_Year&&'fact_table'[week]=_Week),'fact_table'[value],,DESC)2. Create new table with summarizeYear =var _t1 = SUMMARIZE('fact_table','fact_table'[year])var _t2 = SUMMARIZE('fact_table','fact_table'[week])var _rank = SELECTCOLUMNS({(1),(2),(3)},"Rank",[Value])ReturnGENERATE(_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])ReturnCALCULATE(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.
1 Reply
IrwanSuper User
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]ReturnRANKX(FILTER('fact_table','fact_table'[year]=_Year&&'fact_table'[week]=_Week),'fact_table'[value],,DESC)2. Create new table with summarizeYear =var _t1 = SUMMARIZE('fact_table','fact_table'[year])var _t2 = SUMMARIZE('fact_table','fact_table'[week])var _rank = SELECTCOLUMNS({(1),(2),(3)},"Rank",[Value])ReturnGENERATE(_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])ReturnCALCULATE(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.