2 levels Selector : TONS/Turnover - Current/YTD

[blackbool44]

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 !

[amitchandak]

@blackbool44 , Not sure I got it. But refer if this can help

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[parry2k · Accepted solution]

@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 work

I 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_1RLZaI

Have 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 YTD

Thanks a lot

[amitchandak]

@blackbool44 , refer if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/