Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Unique Values with Multiple Conditions

Hi everyone, 

 

I'm wondering if there is a way to group by unique values in one column and still conduct analysis on the other columns. Looking at the sample data below, I am trying to group by case, and create a new column that will be either "yes" or "null" with the condition that a drug either appears twice for a case OR has a numerical value (i.e., not QTOF under the screen column). 

 

In this example, case A would be null for all drugs, case B would be yes for all drugs, case C yes only for advil, and case D yes for all. (see below)

 

Is there a way I can do this? Thanks in advance!

 

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous 

     

    You can add a column with below DAX. Modify table name per your need. Power BI does not support merging cells like in Excel, so it will show a "yes" or "null" value on every row.  

    Column = 
VAR _condition1 = IF(CALCULATE(COUNT('Table (2)'[Case]),ALLEXCEPT('Table (2)','Table (2)'[Case],'Table (2)'[Drug])) > 1, 1, 0)
VAR _condition2 = IF(IFERROR(VALUE(RIGHT('Table (2)'[Screen],1)),-1) = -1, 0, 1)
RETURN
IF(_condition1=1 || _condition2=1, "yes", "null")

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Anonymous 

     

    You can add a column with below DAX. Modify table name per your need. Power BI does not support merging cells like in Excel, so it will show a "yes" or "null" value on every row.  

    Column = 
VAR _condition1 = IF(CALCULATE(COUNT('Table (2)'[Case]),ALLEXCEPT('Table (2)','Table (2)'[Case],'Table (2)'[Drug])) > 1, 1, 0)
VAR _condition2 = IF(IFERROR(VALUE(RIGHT('Table (2)'[Screen],1)),-1) = -1, 0, 1)
RETURN
IF(_condition1=1 || _condition2=1, "yes", "null")

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello,

       

      I am trying this out now and it is telling me "Too many arguments were passed to the COUNT function. The maximum argument count for the function is 1."

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        3 years ago

        Hi Anonymous 

         

        Did you miss the right parenthesis next to Case column?

         

        IF(CALCULATE(COUNT('Table (2)'[Case]),ALLEXCEPT('Table (2)','Table (2)'[Case],'Table (2)'[Drug])) > 1, 1, 0)

         

        Best Regards,
        Community Support Team _ Jing