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

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.

     

7 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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

  • 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.