cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
richardhemi Frequent Visitor
Frequent Visitor

Conditional column based on a subset of column value

Hi Experts, 

 

I cannot fully get row/query context when doing a calculation. I have got some data to illustrate and hoping to get some help, below is my data. I want created a conditional column to display "Order type" for "Transaction ID", so for each "Transaction ID", there are the conditions.

1. If "order type" F then  F for all "transaction item no" in that "transaction id"

2. If "order type" C then  C for all "transaction item no" in that "transaction id"

3. If transaction id have both C and F then "F" for all "transaction item no"

4. if order type is null for that transaction id e.g. transaction id 5 in below, then "F"

 

Transaction ID    Transaction Item No        Order type          Conditional Column 

1                         001                                   F                          F

1                         002                                   Null

1                         003                                   Null

2                         001                                   C                          C

2                         002                                   Null

3                         001                                   C                          C

3                         002                                   F                           F

4                         001                                   C

4                         002                                   C

5                         001                                   Null                       

5                         002                                   Null                       

Ideal result

Transaction ID    Transaction Item No        Order type          Conditional Column 

1                         001                                   F                          F

1                         002                                   Null                     F

1                         003                                   Null                     F

2                         001                                   C                         C

2                         002                                   Null                     C

3                         001                                   C                         F

3                         002                                   F                          F

4                         001                                   C                         C

4                         002                                   C                         C

5                         001                                   Null                     F

5                         002                                   Null                     F

 

Can't achieve this conditional column I want with conditional column as it only gives if then else and don't take calculate column context, which syntax do I need to use?

 

 

Thanks 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Conditional column based on a subset of column value

@richardhemi 

replace "Undefined" to "F"

Conditional Column = 
var _orderF = calculate(countrows('Table'),allexcept('Table','Table'[Transaction ID]),'Table'[Order type]="F")
var _orderC = calculate(countrows('Table'),allexcept('Table','Table'[Transaction ID]),'Table'[Order type]="C")
return
if(_orderF > 0, "F", IF(_orderC > 0, "C", "F"))

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

4 REPLIES 4
Super User II
Super User II

Re: Conditional column based on a subset of column value

Hi @richardhemi 

try a measure

Conditional Column = 
var _orderF = calculate(countrows('Table'),allexcept('Table','Table'[Transaction ID]),'Table'[Order type]="F")
var _orderC = calculate(countrows('Table'),allexcept('Table','Table'[Transaction ID]),'Table'[Order type]="C")
return
if(_orderF > 0, "F", IF(_orderC > 0, "C", "Undefined"))

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

richardhemi Frequent Visitor
Frequent Visitor

Re: Conditional column based on a subset of column value

Hi @az38 , that didn't work, as it counted transaction ID that does not have any values in them as well, so transaction ids that dont have "F" or "C" would be F as well. 

Super User II
Super User II

Re: Conditional column based on a subset of column value

@richardhemi 

replace "Undefined" to "F"

Conditional Column = 
var _orderF = calculate(countrows('Table'),allexcept('Table','Table'[Transaction ID]),'Table'[Order type]="F")
var _orderC = calculate(countrows('Table'),allexcept('Table','Table'[Transaction ID]),'Table'[Order type]="C")
return
if(_orderF > 0, "F", IF(_orderC > 0, "C", "F"))

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

PR20048119 New Contributor
New Contributor

Re: Conditional column based on a subset of column value

Output =
var Check_C =
ISEMPTY(FILTER('New Text Document','New Text Document'[ORder Type]="C" && 'New Text Document'[Transaction ID]=EARLIER('New Text Document'[Transaction ID])))

var Check_F =
NOt(ISEMPTY(FILTER('New Text Document','New Text Document'[ORder Type]="F" && 'New Text Document'[Transaction ID]=EARLIER('New Text Document'[Transaction ID]))))
return
IF(OR(Check_C,Check_F)<>TRUE,"C","F")
 
 
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors