CALCULATE with IN, NOT, OR, AND

[busarch]

Hello,

I'm trying to get a conditional count of items.

The scenario is: I have a data set with customer complaints about products. The dataset is simple 3 column spreadsheet

1) customerID 2) ProductName 3) ComplaintDate

There are 13 products A,B,C,D.....M

I'd like to calculate two things

1) Number of customers that call about product A or B or C but not X or Y or Z 

2) Number of customers that call about product A and B and C but not X and Y and Z

If I could make up a syntax for this, it will look similar to: 

1) CALCULATE(DISTINCTCOUNT(CustomerID),AND(Product IN ANY ("A","B","C"),  NOT ANY ("X","Y","Z"))

2) CALCULATE(DISTINCTCOUNT(CustomerID),AND(Product IN ("A","B","C"),  NOT("X","Y","Z"))

What I have tried so far.

VAR Called = 'A,B,C'

VAR DidntCall= X,Y,Z

RETURN

CALCULATE(DISTINCTCOUNT([CustomerID]),AND([Product] IN {Called},NOT([Product] IN {DidntCall})))

Thank you!!!

[v-lili6-msft]

HI @busarch 

For your case, just try this logic:

1)

Measure 1 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[customerID] ),
    FILTER (
        'Table',
         AND (
            'Table'[ProductName] = "A" || 'Table'[ProductName] = "B"  || 'Table'[ProductName] = "C",
            'Table'[ProductName] <> "X"  || 'Table'[ProductName] <> "Y" || 'Table'[ProductName] <> "Z"
         )))

2)

use this logic (this exclude customerid that not call "X""Y""Z" one or more of them)

Measure 2 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[customerID] ),
    FILTER (
        'Table',
         AND(
            "A" in VALUES('Table'[ProductName]) &&  "B" in VALUES('Table'[ProductName]) &&  "C" in VALUES('Table'[ProductName]),
            NOT("X") in VALUES('Table'[ProductName])   &&   NOT("Y") in VALUES('Table'[ProductName])   &&  NOT("Z") in VALUES('Table'[ProductName])  
           
      )))

or use this logic (this only exclude customerid that not call "X""Y""Z" together)

Measure 2 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[customerID] ),
    FILTER (
        'Table',
         AND(
            "A" in VALUES('Table'[ProductName]) &&  "B" in VALUES('Table'[ProductName]) &&  "C" in VALUES('Table'[ProductName]),
            NOT("X") in VALUES('Table'[ProductName])   ||   NOT("Y") in VALUES('Table'[ProductName])   ||  NOT("Z") in VALUES('Table'[ProductName])  
           
      )))

Regards,

Lin

[busarch]

This is awesome.

Is it possible to incorporate variables into this? That way the "product they called about" and "Product they DID NOT call about" can be variable and controled by a user via a slicer. (I've created these slicers and set the VAR = slicer result (comma delimited)

I've set up a a comma delimited variable tied to a slicer for both the 

"CALCULATE(DISTINCTCOUNT([CustomerID]),AND([Product] IN {Called},NOT([Product] IN {DidntCall})))"

[v-lili6-msft]

hi @busarch 

Yes, you could try this way:

First, you must have two separate dim productname tables for slicer: In ProductName / NOT IN ProductName

1)

New Measure 1 = 
VAR _customerID=CALCULATETABLE(VALUES('Table'[customerID]),'Table'[ProductName] IN VALUES('NOT In ProductName'[ProductName])) RETURN
CALCULATE (
    DISTINCTCOUNT ( 'Table'[customerID] ),
    FILTER (
        'Table',
         AND (
            'Table'[ProductName] in  VALUES('In ProductName'[ProductName]) ,
             NOT('Table'[customerID]) IN _customerID
         )))

2)

New Measure 2 = 
VAR __customerIDIN=CALCULATETABLE(VALUES('Table'[customerID]),'Table'[ProductName] IN VALUES('In ProductName'[ProductName]))
VAR __customerIDNOTIN=CALCULATETABLE(VALUES('Table'[customerID]),'Table'[ProductName] IN VALUES('NOT In ProductName'[ProductName])) RETURN
CALCULATE (
    DISTINCTCOUNT ( 'Table'[customerID] ),
    FILTER (
        'Table',
        AND(
         NOT('Table'[customerID]) IN __customerIDIN,
         NOT('Table'[customerID]) IN __customerIDNOTIN
      )))

OR

New Measure 2 = 
VAR __customerIDIN=CALCULATETABLE(VALUES('Table'[customerID]),'Table'[ProductName] IN VALUES('In ProductName'[ProductName]))
VAR __customerIDNOTIN=CALCULATETABLE(VALUES('Table'[customerID]),'Table'[ProductName] IN VALUES('NOT In ProductName'[ProductName])) RETURN
CALCULATE (
    DISTINCTCOUNT ( 'Table'[customerID] ),
    FILTER (
        'Table',
        AND(
         NOT('Table'[customerID]) IN __customerIDIN,
         NOT('Table'[ProductName]) IN VALUES('NOT In ProductName'[ProductName])
      )))

Regards,

Lin

[TomMartens]

Hey @busarch ,

please provide a pbix file that contains sample data, but still reflects your data model. Upload the pbix and/or xlsx file(s) to onedrive or dropbox and share the link.

Regards,

Tom