Forum Discussion
Combining two measures and adding an IF
Hello, I am still new to power BI and how its formatting works so Im not sure how to accomplish this. I have two measures:
SinglePOs =
VAR PO = MAX(PO_PurchaseOrderDetail[PurchaseOrderNo])
VAR PREVPO = CALCULATE(MAX(PO_PurchaseOrderDetail[PurchaseOrderNo]), PO_PurchaseOrderDetail[PurchaseOrderNo] < PO)
VAR Rec = MAXX(FILTER(PO_PurchaseOrderDetail,[PurchaseOrderNo] = PO),[Received])
VAR PREVREC = MAXX(FILTER(PO_PurchaseOrderDetail,[PurchaseOrderNo] = PREVPO),[Received])
VAR Qty = MAXX(FILTER(PO_PurchaseOrderDetail,[PurchaseOrderNo] = PO),[QuantityOrdered])
VAR PREVQTY = MAXX(FILTER(PO_PurchaseOrderDetail,[PurchaseOrderNo] = PREVPO),[QuantityOrdered])
VAR Req = MAXX(FILTER(PO_PurchaseOrderDetail,[PurchaseOrderNo] = PO),[RequiredDate])
VAR PREVREQ = MAXX(FILTER(PO_PurchaseOrderDetail,[PurchaseOrderNo] = PREVPO),[RequiredDate])
RETURN
IF ( [QTYAvailable_SumFinal] < 0 && Qty < ABS([QTYAvailable_SumFinal]), Rec & " " &PO & " " & Qty & "pcs " & Req & " " & PREVREC & " " & PREVPO & " " & PREVQTY & "pcs " & " " & PREVREQ, Rec & " " & PO & " " & Qty & "pcs " & Req )& another
SplitPO =
VAR PO = MAX(PO_PurchaseOrderDetail[PurchaseOrderNo])
VAR RowCount = CALCULATE(COUNTROWS(PO_PurchaseOrderDetail),
PO_PurchaseOrderDetail[PurchaseOrderNo] = PO)
VAR FirstReq = MINX(FILTER(PO_PurchaseOrderDetail,PO_PurchaseOrderDetail[PurchaseOrderNo] = PO),[RequiredDate])
VAR Qty = MAXX(FILTER(PO_PurchaseOrderDetail,PO_PurchaseOrderDetail[PurchaseOrderNo] = PO && FirstReq = PO_PurchaseOrderDetail[RequiredDate] ),[QuantityOrdered])
VAR SecondReq = MINX(FILTER(PO_PurchaseOrderDetail,PO_PurchaseOrderDetail[PurchaseOrderNo] = PO && PO_PurchaseOrderDetail[RequiredDate] > FirstReq ),[RequiredDate])
VAR SecondQTY = MAXX(FILTER(PO_PurchaseOrderDetail,PO_PurchaseOrderDetail[PurchaseOrderNo] = PO && SecondReq = PO_PurchaseOrderDetail[RequiredDate] ),[QuantityOrdered])
RETURN
PO & " " & FirstReq & " " & Qty & "pcs" & " " & RowCount & " " & " || " & SecondReq & " " & PO & " " & SecondQTY & "pcs"
I basically need to combine these two and have a check at the top that checks the RowCount variable. If it is >1, then I need it to run the SplitPO measure. If it is just 1 row, then it can run the SinglePOs measure. Is this possible to do?
- Anonymous3 years ago
Hi Anonymous ,
Please try this measure.
Measure = VAR RowCount = CALCULATE ( COUNTROWS ( PO_PurchaseOrderDetail ), PO_PurchaseOrderDetail[PurchaseOrderNo] = PO ) VAR result = SWITCH ( TRUE (), RowCount > 1, [SplitPO], 1, [SinglePOs] ) RETURN resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
1 Reply
- AnonymousNot applicable
Hi Anonymous ,
Please try this measure.
Measure = VAR RowCount = CALCULATE ( COUNTROWS ( PO_PurchaseOrderDetail ), PO_PurchaseOrderDetail[PurchaseOrderNo] = PO ) VAR result = SWITCH ( TRUE (), RowCount > 1, [SplitPO], 1, [SinglePOs] ) RETURN resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum