Forum Discussion
Crete a table from another table with selected slicer
Hello,
Here is my data set.
table1
| week_num | week_rank |
| 202151 | 1 |
| 202152 | 2 |
| 202201 | 3 |
| 202202 | 4 |
| 202203 | 5 |
| 202204 | 6 |
| 202205 | 7 |
| .... | |
| 202210 | 12 |
the format of [week_num] is "YYYY+week number" and I use the table with single slicer to get data with selected [week_num].
However I would like to get another table from the table with selected value of slicer
table2(what I would like to get)
if I select 202204 from slicer then I only want to get 5 rows from what I select from table 1
table2
| week_num | week_rank |
| 202152 | 2 |
| 202201 | 3 |
| 202202 | 4 |
| 202203 | 5 |
| 202204 | 6 |
So here is my table create formula
table2=
FILTER(
ALL('table1'[week_num],'table1'[week_rank]),
'table1'[week_rank])<=max('table'[week_rank]))&&
('table1'[week_rank])>max('table'[week_rank])-5)
)
but it only returns like below.
| week_num | week_rank |
| 202206a | 8 |
| 202207 | 9 |
| 202208 | 10 |
| 202209 | 11 |
| 202210 | 12 |
Could you kindly help me please?
zzzsou , a calculated table can not use slicer value, It is static.
This logic can work in table as var in measure
for WOW
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
1 Reply
zzzsou , a calculated table can not use slicer value, It is static.
This logic can work in table as var in measure
for WOW
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8