Create a slicer using a measure to filter selected value

Question

Hi Guys, I'm working on a table with last year two period and this year two period datas.

I want to create a slicer using column[ Products] to see this session's Region Level 2 and last session's Region Level 1 comparison, which means I have two conditions to consider.

So I have five colomuns to consider, and also other columns like SALES, comments and others to show in the table visual.

Products	Region	Region Level	Session	Index	Sales	Comments
Product A	Headquarter	1	2022 Annual	1
Product A	West	2	2022 Annual	1
Product B	East	2	2022 Annual	1
Product C	Headquarter	1	2022 Annual	1
Product A	Headquarter	1	2023 Interim	2
Product B	West	2	2023 Interim	2
Product C	West	2	2023 Interim	2
Product A	West	2	2023 Annual	3
Product B	Headquarter	1	2023 Annual	3
Product C	Headquarter	1	2023 Annual	3

picture

Because I have more than twenty products in the database.

Can it work when I choose Product A it would show the value of both this session's Level 2 and Last session's level 1 data?

I added a column index for 2022 Annual as 1, 2023 Interim as 2, 2023 Annual as 3. would be more index in the future.

I create a measure Using following:

Second Filter =

VAR selectlevel = SELECTEDVALUE(Table1[Region Level])

VAR selectsession = SELECTEDVALUE(Table1[Index])

RETURN

SWITCH(

TRUE(),

selectlevel = "1" && selectsession = selectsession-1,1,

selectlevel = "2" && selectsession = selectsession,1,

0)

But it shows ERROR.

So is it possible to create a dynamic measure to use in the slicer filter?

Thank you all.

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;，
&nbsp;
Directly filter with a slicer to obtain the following

If the above method is not what you want, you want to compare on the same line, such as comparing the sales of level 1 and level 2 under the same session.&nbsp;Then your index needs to be created as follows:
Group by Session column and add a All Rows column.

Add a custom column to add index and then expand it.

Create a measure to get level 1 sales.
level 1 sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Session]=MAX('Table'[Session])&amp;&amp;[Region Level]=MAX('Table'[Region Level])-1))

&nbsp;
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Create a slicer using a measure to filter selected value

1 Reply

Related content

Help to create DAX Measure

Need help creating a measure

Can i use a measure selected in a slicer (created with field parameters) to create a rank?

Create a measure to select the maximum value of another measure

Create count measure