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 ,
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]
)))
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 |
---|---|
113 | |
99 | |
76 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |