Unique Values with Multiple Conditions

[Anonymous]

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!

 

[v-jingzhang · Accepted solution]

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]

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]

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