Forum Discussion

Jungbin's avatar
Jungbin
Frequent Visitor
2 years ago
Solved

SELECTEDVALUE & FILTER based on slicer

Hi   There are [Opportunity ID], [Opportunity Owner], and [Sales Collaborator] fields in my data. The [Sales Collaborator] field can be either blank or filled out, and some of [Sales Collaborator...
  • mark_endicott's avatar
    mark_endicott
    2 years ago

    Jungbin - This should work for you

     

    VAR selected_person =
    SELECTEDVALUE ( 'Current'[Opportunity Owner] )
RETURN
    IF (
        ISFILTERED ( 'Current'[Opportunity Owner] ),
        CALCULATE (
            DISTINCTCOUNT ( 'Current'[Opportunity ID] ),
            REMOVEFILTERS ( 'Current'[Opportunity Owner] ),
            'Current'[Sales Collaborator] = selected_person
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Current'[Opportunity ID] ),
            FILTER (
                'Current',
                CONTAINSSTRING (
                    'Current'[Sales Collaborator],
                    SELECTEDVALUE ( 'Current'[Opportunity Owner], "" )
                )
            )
        )
    )

     

    Proof it works:

     

    It works by using a variable to store the selected value, and then the if statement decides which routeway to take, depending on whether there is a filter on "opportunity owner" or not. 

     

    If there is a filter applied, we need to remove any applicable row based filters on the column and then set the new filter from the variable. 

     

    Please accept this as the solution so others can find it. 

     

mark_endicott's avatar
mark_endicott
Icon for Super User rankSuper User
2 years ago

Jungbin - This should work for you

 

VAR selected_person =
    SELECTEDVALUE ( 'Current'[Opportunity Owner] )
RETURN
    IF (
        ISFILTERED ( 'Current'[Opportunity Owner] ),
        CALCULATE (
            DISTINCTCOUNT ( 'Current'[Opportunity ID] ),
            REMOVEFILTERS ( 'Current'[Opportunity Owner] ),
            'Current'[Sales Collaborator] = selected_person
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Current'[Opportunity ID] ),
            FILTER (
                'Current',
                CONTAINSSTRING (
                    'Current'[Sales Collaborator],
                    SELECTEDVALUE ( 'Current'[Opportunity Owner], "" )
                )
            )
        )
    )

 

Proof it works:

 

It works by using a variable to store the selected value, and then the if statement decides which routeway to take, depending on whether there is a filter on "opportunity owner" or not. 

 

If there is a filter applied, we need to remove any applicable row based filters on the column and then set the new filter from the variable. 

 

Please accept this as the solution so others can find it. 

 

Jungbin's avatar
Jungbin
Frequent Visitor
2 years ago

Hi Mark!

Thank you for the solution.

 

Due to some interactions between [Opportunity Owner] and [Sales Team] fields in my data (I put them in Slicer separately), I edited your syntax like as below.

Count = 
VAR SelectedTeam = VALUES('Current'[Sales Team])
VAR SelectedOwners = CALCULATETABLE(VALUES('Current'[Opportunity Owner]),'Current'[Sales Team] IN SelectedTeam)
VAR IsSingleOwnerSelected = HASONEVALUE('Current'[Opportunity Owner])
VAR SelectedOwner = SELECTEDVALUE('Current'[Opportunity Owner])

RETURN
CALCULATE(DISTINCTCOUNT('Current'[Opportunity ID18]),
          FILTER(ALL('Current'),
                 ('Current'[Sales Collaborator] IN SelectedOwners || 
                 ( IsSingleOwnerSelected && 'Current'[Sales Collaborator] = SelectedOwner))
          )
)

 

Thanks for your help.