locked
using || and && within the same Powerpivot Calculate syntax RRS feed

  • Question

  • hi. i'm trying to isolate two groups of users. the 1st is a group who has suffered one of 3 "negative" experiences, without regarding whether or not they also had "positive" ones - i.e., they are the users from the entire population, who encountered one of 3 negative statuses in their user lifetimes. 

    the 2nd is based on this group, but users who have ONLY encountered these bad experiences, and never any good experience. for some reason, when i use && to "trim down" the positive statuses from the user group, i end up with the same size groups, i.e. the following two codes yield the same powerpivot measure result: 

    =CALCULATE(DISTINCTCOUNT(CDRs[USERID]), 
    CDRs[disposition] = "negative exp 1" || 
    CDRs[disposition] = "negative exp 2"|| 
    CDRs[disposition] = "negative exp 3")


    =CALCULATE(DISTINCTCOUNT(CDRs[USERID]), 
    CDRs[disposition] = "negative exp 1" || 
    CDRs[disposition] = "negative exp 2"|| 
    CDRs[disposition] = "negative exp 3"&&
    CDRs[disposition] <> "positive exp 1"&&
    CDRs[disposition] <> "positive exp 2"&&
    CDRs[disposition] <> "positive exp 3")

    i would love some help on this...

    thx!

    Sunday, September 21, 2014 11:29 AM

Answers

  • Hi Guy,

    A first stab could be...

    Users with Negative Feedback Only:=
    CALCULATE(
      DISTINCTCOUNT(CDRs[USERID]), 
      CDRs[disposition] = "negative exp 1" 
        || CDRs[disposition] = "negative exp 2" 
        || CDRs[disposition] = "negative exp 3",
      FILTER(
        VALUES(CDRs[USERID]),
        CALCULATE(
          COUNTROWS(VALUES(CDRs[USERID])),
          CDRs[disposition] = "positive exp 1"
          || CDRs[disposition] = "positive exp 2"
          || CDRs[disposition] = "positive exp 3"
        ) = 0
      )
    )



    This solution can almost definitely be re-written more optimally to avoid using a FILTER but should do what you're asking. Let me know if it works as expected and performs acceptably over your data set. The FILTER and inner CALCULATE are just filtering out users that have at least one row with positive feedback. I've assumed that this is for a Calculated Field and the syntax is for defining one within the Calculation Area beneath the table in the Power Pivot window.

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Sunday, September 21, 2014 4:47 PM