Forum Discussion

RingoMoon's avatar
RingoMoon
Frequent Visitor
2 years ago
Solved

Override normal slicer behavior

So I have a sales data and it can be filtered by organization or industry (each organization has an industry). Now if I have a slicer for organization, I can select an organization and it will filter the sales data to only show the sales data for that specific organization. But I want to override that behavior.

 

When I select an organization in the slicer, instead of showing the sales data for that organization, I want to show instead the sales data for the industry where that organization belongs.

 

How do I achieve this?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  RingoMoon ,

     

    Here are the steps you can follow：

    1. Create measure.

    Rank =
RANKX(
    FILTER(ALLSELECTED('Industry table'),'Industry table'[Org_ID]=MAX('Industry table'[Org_ID])&&'Industry table'[Industry Name]<>"Other"),
    CALCULATE(MAX('Industry table'[Industry Name])),,ASC)
    Flag =
var _select=SELECTEDVALUE('Organization table'[Name])
var _ID=MINX(FILTER(ALL('Organization table'),'Organization table'[Name]=_select),[ID])
var _min=MINX(ALLSELECTED('Industry table'),[Rank])
return
IF(
    MAX('Industry table'[Org_ID])=_ID&&[Rank]=_min&&MAX('Industry table'[Industry Name])<>"Other",1,0)

    2. Result:

    When there are both Other and other values in [Industry Name], the smallest of the values other than Other is displayed:

    When [Industry Name] is only Other, nothing is displayed:

     

     

    Best Regards,

    Liu Yang

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  RingoMoon ,

     

    Here are the steps you can follow：

    1. Create measure.

    Rank =
RANKX(
    FILTER(ALLSELECTED('Industry table'),'Industry table'[Org_ID]=MAX('Industry table'[Org_ID])&&'Industry table'[Industry Name]<>"Other"),
    CALCULATE(MAX('Industry table'[Industry Name])),,ASC)
    Flag =
var _select=SELECTEDVALUE('Organization table'[Name])
var _ID=MINX(FILTER(ALL('Organization table'),'Organization table'[Name]=_select),[ID])
var _min=MINX(ALLSELECTED('Industry table'),[Rank])
return
IF(
    MAX('Industry table'[Org_ID])=_ID&&[Rank]=_min&&MAX('Industry table'[Industry Name])<>"Other",1,0)

    2. Result:

    When there are both Other and other values in [Industry Name], the smallest of the values other than Other is displayed:

    When [Industry Name] is only Other, nothing is displayed:

     

     

    Best Regards,

    Liu Yang

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