Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous
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
Hi @Anonymous
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
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.
@Anonymous
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |