Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Does anyone know how to achieve top n by date? More details below...
Table 1 | ||
Date | Phrase | Value |
Jan-19 | phrase 1 | 10 |
Jan-19 | phrase 2 | 20 |
Jan-19 | phrase 3 | 30 |
Jan-19 | phrase 4 | 40 |
Jan-19 | phrase 5 | 70 |
Feb-19 | phrase 1 | 100 |
Feb-19 | phrase 2 | 30 |
Feb-19 | phrase 3 | 20 |
Feb-19 | phrase 4 | 10 |
Feb-19 | phrase 5 | 5 |
Mar-19 | phrase 1 | 150 |
Mar-19 | phrase 2 | 20 |
Mar-19 | phrase 3 | 30 |
Mar-19 | phrase 4 | 40 |
Mar-19 | phrase 5 | 200 |
Table 2 | |
Phrase | Legend |
phrase 1 | Category A |
phrase 2 | Category B |
phrase 3 | Category B |
phrase 2 | Category C |
phrase 4 | Category C |
phrase 5 | Category D |
phrase 3 | Category E |
Current approach: Tables 1 and 2 are connected by phrase. I am using the categories as the legend and summed values as the value. I'm currently using a top n filter (e.g. top 2) which is showing the top 2 based on total value over the entire period.
Desired output: What I'm trying to do is show the top n at each given date. So based on the sample of tables I shared, this would look something like:
Date | Top 2 | Total Value |
Jan-19 | Category D | 70 |
Jan-19 | Category C | 60 |
Feb-19 | Category A | 100 |
Feb-19 | Category B | 50 |
Mar-19 | Category D | 200 |
Mar-19 | Category A | 150 |
Solved! Go to Solution.
Hi @badger123
1.Create measures in Table2
Measure_value = SUM('Table 1'[Value]) rank = RANKX(ALL('Table 2'[Legend]),[Measure_value],,DESC)
2.Then create a table by entering data in Modeling menu
Don't create any relationship for this table
add "top" column from this table into a slicer
3.Create measures in Table2
selected topn = SELECTEDVALUE('top_n table'[top]) flag = IF('Table 2'[rank]<='Table 2'[selected topn],1,0)
Add "flag" measure in the visual level filter of that table as above
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @badger123
1.Create measures in Table2
Measure_value = SUM('Table 1'[Value]) rank = RANKX(ALL('Table 2'[Legend]),[Measure_value],,DESC)
2.Then create a table by entering data in Modeling menu
Don't create any relationship for this table
add "top" column from this table into a slicer
3.Create measures in Table2
selected topn = SELECTEDVALUE('top_n table'[top]) flag = IF('Table 2'[rank]<='Table 2'[selected topn],1,0)
Add "flag" measure in the visual level filter of that table as above
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@badger123 - Just to clarify, a single phrase has multiple Categories. This could be problematic - could you share a screenshot of your model diagram (relationships)?
Thanks,
Nathan
Hi @Anonymous , thanks for your help. Yes, a single phrase can have more than one category assigned to it... The tables are linked by phrase (many to many).
@badger123 - One potential solution is to add a new Calculated Table:
Top Legends = var x = SUMMARIZE(Table1, Table2[Legend], Table1[Date], "TotalValue", sum(Table1[Value])) var y = ADDCOLUMNS( x, "Rank", RANKX( FILTER( x, [Date]=EARLIER([Date]) ), [TotalValue] ) ) var z = FILTER(y, [Rank] <= 2) return z
@Anonymous I tried the measure, but keep getting : The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
User | Count |
---|---|
98 | |
89 | |
82 | |
71 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |