Forum Discussion
Helper I2 levels Selector : TONS/Turnover - Current/YTD
Hello Dear Power BI Community,
I have a request concerning SWITCH selectors,
I have my data following this pattern,
Sales
| Date | Tonnage | Turnover |
| 01/01/2019 | 12 | 44 |
| 03/05/2019 | 14 | 48 |
| 08/09/2019 | 33 | 54 |
| 01/01/2020 | 21 | 31 |
| 02/05/2020 | 51 | 44 |
| 09/11/2020 | 22 | 78 |
And I have a calendar table (Calendar)
In order to create my first selector
I have created a Switch Table (Selector Curr/YTD) :
| Selector | SelectorNB |
| Current | 1 |
| YTD | 2 |
I have created the following measures :
selector Nb = MIN(Selector Curr/YTD[SelectorNB])
Tonnage Curr = sum(Sales[Tonnage])
Tons YTD = CALCULATE(SUM(Sales[Tonnage]), DATESYTD('Calendar'[Calendar.Date]))
This works perfect and I can generate a selector on my dashboard
However, I would like the user to be able to switch first between tons and Turnover on a first selector
then to switch between current and YTD on a second selector.
Do you know if this is possible ?
How would you approach the issue ?
is there any tutorial out there that could help me achieve this ?
Thanks a lot for your great help !
blackbool44 unpivot your table
- transform data
- select date column
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply
you can use Attribute column for slicer and rest everything will workI would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
5 Replies
- amitchandak
Super User
blackbool44 , Not sure I got it. But refer if this can help
- parry2k
blackbool44 unpivot your table
- transform data
- select date column
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply
you can use Attribute column for slicer and rest everything will workI would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
- blackbool44
Dear parry2k ,
Thanks a lot, I have followed your instructions and unpivoted my table to get the turnover and the tonnage on the same column.
I created a slicer for the type of value (tonnage or turnover).
And then created a disconnected table with my second slicer categories (current/YTD).
Which I activate thanks to a SWITCH dax formula.
For those who want to know more about the switch formula for filtering tables : I followed this great tutorial from Avi Singh : https://www.youtube.com/watch?v=Tn0P_1RLZaIHave a great day !
- blackbool44
Hi amitchandak ,
it works for a 1 level slicer (Curr/YTD).
Howeve, I need to ann another Level (Tons/turnover).
The user first choose 1 level : Tons or Turnover
Then choose the second level : Curr or YTDThanks a lot
- amitchandak
blackbool44 , refer if this can help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
