Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
9 years ago
Solved

Writing a Condition

Here's a snapshot of my data:

 

I need a way to say whether or not an OrganizationName is considered CSP (IsCSP= CSP). However, each JobNum has multiple RateDesc, some rates are CSP and some aren't. I want to write a column or measure or something that say "If at least one RateDesc= "CSP", then this OrganizationName is CSP.  I need the OrganizationName as a whole to be considered CSP or NotCSP

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi bhmiller89,

     

    From your description, I create some sample data, you can create calculated columns like below:

     

    IsCSPNew = IF('Table3'[IsCSP]="CSP",1,0)

     

    OrgName = IF(CALCULATE(SUM(Table3[IsCSPNew]),ALLEXCEPT(Table3,'Table3'[TSMDepartment],'Table3'[OrganizationName],'Table3'[Domain Name],'Table3'[JobNum])),
                      "CSP",
                      "NotCSP"
                  )

     

     

     

    Best Regards,
    Qiuyun Yu 

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi bhmiller89,

     

    You can try the CONTAIN formula to have a measure that does that classification:

     

    Measure = IF(
                     CONTAINS(
                                      Table,
                                      Table[IsCSP];
                                      "CSP");
                      "CSP";
                      "NotCSP"
                  )

    Regards,

    MFElix

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi bhmiller89,

     

    From your description, I create some sample data, you can create calculated columns like below:

     

    IsCSPNew = IF('Table3'[IsCSP]="CSP",1,0)

     

    OrgName = IF(CALCULATE(SUM(Table3[IsCSPNew]),ALLEXCEPT(Table3,'Table3'[TSMDepartment],'Table3'[OrganizationName],'Table3'[Domain Name],'Table3'[JobNum])),
                      "CSP",
                      "NotCSP"
                  )

     

     

     

    Best Regards,
    Qiuyun Yu