SELECTEDVALUE & FILTER based on slicer

[Jungbin]

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] values are one of [Opportunity Owner] values. Here, I want to create Measure [Count] to distinctcount Opportunities where [Sales Collaborator] is one of the [Opportunity Owner].

For instance, the table below has 26 unique Opportunity IDs.

There are 12 unique Opportunities where [Sales Collaborator] has values.

Only 8 distinct Opportunities have a [Sales Collaborator] value which is one of [Opportunity Owner]. I want to make the [Count] measure to count this and I want it to be changed based on whom I select in the [Opportunity Owner] slicer.

ChatGPT told me to use DAX below : 

Count = CALCULATE(DISTINCTCOUNT('Current'[Opportunity ID]),FILTER('Current',CONTAINSSTRING('Current'[Sales Collaborator],SELECTEDVALUE('Current'[Opportunity Owner],""))))

When I place the measure in a card visual, it shows me the correct number when I don't select anyone in the [Opportunity Owner] slicer. However, The visual shows me (Blank) whenever I select someone in the slicer.

From the example, I want my card visual to show me '2' when I select 'James Mitchell' in the [Opportunity Owner] slicer because that's the unique number of Opportunities while it's one of the Opportunity Owners.

How can I make it?

The following is sample data.

Opportunity ID	Opportunity Owner	Sales Collaborator
AAA	Emliy Parker
BBB	James Mitchell
CCC	James Mitchell
DDD	James Mitchell
EEE	Olivia Thompson	Benjamin Taylor
EEE	Olivia Thompson	Benjamin Taylor
EEE	Olivia Thompson	Benjamin Taylor
FFF	Olivia Thompson	Daniel Johnson
GGG	Daniel Johnson
HHH	Daniel Johnson
III	Daniel Johnson
JJJ	James Mitchell
KKK	Sophia Williams
LLL	Michael Brown	Sophia Williams
LLL	Michael Brown	Sophia Williams
LLL	Michael Brown	Sophia Williams
LLL	Michael Brown	Sophia Williams
LLL	Michael Brown	Sophia Williams
MMM	Michael Brown	Sophia Williams
NNN	Michael Brown	Sophia Williams
NNN	Michael Brown	Sophia Williams
OOO	Michael Brown	Sophia Williams
PPP	Sophia Williams
QQQ	Sophia Williams
RRR	Ava Davis	Benjamin Taylor
SSS	Ava Davis	James Mitchell
TTT	Olivia Thompson	Benjamin Taylor
UUU	Sophia Williams
VVV	Olivia Thompson	Benjamin Taylor
VVV	Olivia Thompson	Benjamin Taylor
WWW	Sophia Williams
WWW	Sophia Williams
WWW	Sophia Williams
XXX	Lily Anderson	James Mitchell
YYY	Michael Brown	Sophia Williams
ZZZ	Sophia Williams

[mark_endicott · Accepted solution]

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

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.