Dynamic Slicer value

[Anonymous]

Hi,

I have 2 different fields(Region and Status) which I want to use as a slicer in my report. Status slicer values should be based/changed on the selected value in Region Slicer. Please refer the below example:

Region

Italy

Spain

France

Germany

Status

Closed

Open

Waiting

Others

The logic should be as mentioned below:

If 1st slicer Italy then 2nd slicer should only show Open

If 1st slicer Germany then 2nd slicer value should only show Closed

If 1st slicer France then2nd slicer value should be Closed & Waiting

If 1st slicer Spain then 2nd slicer value should only show Others

Thanks

@Mariusz 

@az38 

[v-eachen-msft]

Hi @Anonymous m

You could create a measure and put it into filter pane to show the result.

Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Region'[Region] ) = "Italy"
        && SELECTEDVALUE ( 'Status'[Status] ) = "Open", 1,
    SELECTEDVALUE ( 'Region'[Region] ) = "Germany"
        && SELECTEDVALUE ( 'Status'[Status] ) = "Closed", 1,
    SELECTEDVALUE ( 'Region'[Region] ) = "France"
        && SELECTEDVALUE ( 'Status'[Status] ) IN { "Waiting", "Closed" }, 1,
    SELECTEDVALUE ( 'Region'[Region] ) = "Spain"
        && SELECTEDVALUE ( 'Status'[Status] ) = "Others", 1,
    0
)

Here is my test result and test file for your reference.

[Anonymous]

Hi,

Thank you to providing the solution. However, It is not working for me due to some reason. This measures shows only 0 value.

Please check my DAX code if i am doing any mistake here:

M_Open Need Status =

SWITCH (

TRUE (),

SELECTEDVALUE ( 'Region Mapping'[Region] ) = "NA"

&& SELECTEDVALUE ( 'Open Needs'[Open Needs Status] ) = "Open", 1,

SELECTEDVALUE ( 'Region Mapping'[Region] ) = "IND"

&& SELECTEDVALUE ( 'Open Needs'[Open Needs Status] ) = "Do Not Work", 1,

SELECTEDVALUE ( 'Region Mapping'[Region] ) = "AP"

&& SELECTEDVALUE ( 'Open Needs'[Open Needs Status] ) IN { "Existing Team", "Open" }, 1,

SELECTEDVALUE ( 'Region Mapping'[Region] ) = "EU"

&& SELECTEDVALUE ( 'Open Needs'[Open Needs Status] ) IN { "Existing Team", "Open" }, 1,

0

)

The values provided in the question were just dumy values, here i am proving the actual table, column names and values. So, you can consider these one now.

Thanks,

[v-eachen-msft]

Hi @Anonymous ,

The formula seems to be right. Are the "Region" and "Open Needs Status" are all single values? You could upload your files without any Confidential Information to OneDrive for Business and share the link here.

[Anonymous]

Hi,

I have been tried even with the sample table which did you to use to calculate the measure but it shows only 0.

I am uploading the screenshot for your reference. Also, can we use a measue as a slicer or at page level filter?

Thanks

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Column cannot display a dynamic result, you need to use measure. According to my test file, you need to use measure as a rule in the filter pane of the second slicer.

Presumably you have already tried, measure can't be used as a slicer or at page level filter.

[Anonymous]

Yes, it is working for me now.

 Thank you for the soultion.

[az38]

Hi @Anonymous 

show, please, all your data model

it seems like you need some bridge table like

Region	Status
Italy	Open
Germany	Closed
France	Closed
France	Waiting
Spain	Others

you need also dimesion tables with unique region and unique status related to this bridge

and fact table related to dimensions