Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Slicer value

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 b...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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.

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-eachen-msft
        Community Support
        6 years ago

        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.