Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi ,
I have a dataset , which is shown below,
In this we need to create calculate column in power bi for total length of products based on few conditions,
**
if Comp1Qty > 0, Comp2Qty > 0
and Comp1Material = Comp2Material
then Comp1Lenght +Comp2Length
or
Comp1Qty > 0, Comp2Qty > 0, Comp3Qty > 0
and Comp1Material = Comp2Material = Comp3Material
then Comp1Lenght +Comp2Length+ Comp3Length
else if
Comp1Qty > 0 but materials are not equal then Comp1Lenght
Product | Type | Comp1Qty | Comp1 Length | Comp1 - Material | Comp2Qty | Comp2 Length | Comp2 - Material | Comp3Qty | Comp3 Length | Comp3 - Material | Total Length |
a | ea | 1 | 10 | wood | 2 | 13 | wood | 1 | 10 | wood | 33 |
b | ad | 2 | 12 | paper | 1 | 11 | paper | 2 | 12 | paper | 35 |
c | ca | 1 | 15 | wood | 1 | 10 | wood | 1 | 15 | glass | 25 |
d | ed | 1 | 20 | flute | 1 | 13 | flute | 0 | 20 | flute | 33 |
e | fa | 2 | 25 | wood | 1 | 15 | glass | 2 | 15 | glass | 25 |
f | ab | 1 | 10 | paper | 2 | 11 | paper | 1 | 10 | paper | 31 |
Solved! Go to Solution.
Hi,
Please use following DAX formula.
If this formula works, Please accept this as solution.
Thank you.
Hi
In your formula you have mentioned "if CompQty > 0".
There is no column called CompQty in your data set. How did you get that?
Thank you.
It is Comp1Qty >0 , Comp2Qty>0 , Comp3Qty >0 then proceed to second condition Comp1Material = Comp2Material = Comp3Material , to get the total.
For eg
If (
Comp1Qty >0 , Comp2Qty >0 &&
Comp1Material = Comp2Material)
then Comp1Lenght +Comp2Length
Hi,
Please use following DAX formula.
If this formula works, Please accept this as solution.
Thank you.
Thank you 🙂 for the solution. 😀
Also we can create a calculated column
Total Length =
If (
Table1[Comp1Qty] >0 &&
Table1[Comp2Qty] >0 &&
Table1[Comp3Qty] >0 &&
Table1[Comp1 - Material] = Table1[Comp2 - Material ] &&
Table1[Comp1 - Material] = Table1[Comp3 - Material ],
Table1[Comp1 Length] + Table1[Comp2 Length] + Table1[Comp3 Length],
If (
Table1[Comp1Qty] >0 &&
Table1[Comp2Qty] >0 &&
Table1[Comp1 - Material] = Table1[Comp2 - Material ],
Table1[Comp1 Length] + Table1[Comp2 Length],
If (
Table1[Comp1Qty] >0,
Table1[Comp1 Length]
)))
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |