Create a slicer using a measure to filter selected value

[Anonymous]

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 solution]

Hi @Anonymous ，

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. 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])&&[Region Level]=MAX('Table'[Region Level])-1))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.