Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tharveysa
Frequent Visitor

Using Switch and a nested loop not working

I have multiple measured columns I'm trying to combine into one column.  On their own, these columns work as intended.  I combined all into one loop with switch function but I cannot get the syntax correct and am lost as to where the error(s) that is causing the problem are located.  List below is what I have currently, again, these are working as seperate columns.  From what I have read, Switch function does not need(has internal "IF" which can make it easier and cleaner but I've tried both ways.

 

End result of below code "Function "SWITCH" does not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."

 

 

Dept Turns = SWITCH (
	TRUE(),
	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 1,
			ServLine[Resolution Code] = "COMPLETE"
		),
		DATEDIFF(ServLine[Order Date], 
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND (
				ServLine[Dept Order Index] = 1,
				NOT CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE")
			),
			DATEDIFF(ServLine[Order Date],
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 2,
			ServLine[Resolution Code] = "COMPLETE"
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 1 &&
				ServLine[Resolution Code] = "COMPLETE"
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND( 
				ServLine[Dept Order Index] = 2, 
				CALCULATE( 
					CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 1
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 1
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 3,
			ServLine[Resolution Code] = "COMPLETE" 
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 1 &&
				ServLine[Resolution Code] = "COMPLETE"
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 3,
				CALCULATE( 
					CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 1
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 1 &&
					ServLine[Resolution Code] = "COMPLETE"
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 4,
			ServLine[Resolution Code] = "COMPLETE"
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 1 &&
				ServLine[Resolution Code] = "COMPLETE"
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 4,
				CALCULATE( 
					CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 1
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 1 &&
					ServLine[Resolution Code] = "COMPLETE"
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND (
			ServLine[Dept Order Index] = 5,
			ServLine[Resolution Code] = "COMPLETE"
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 2 &&
				ServLine[Resolution Code] = "COMPLETE"
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 5,
				CALCULATE( CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 2
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 2 &&
					ServLine[Resolution Code] = "COMPLETE"
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND (
			ServLine[Dept Order Index] = 6, 
			ServLine[Resolution Code] = "COMPLETE" 
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] < 6 &&
				ServLine[Resolution Code] = "COMPLETE"
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 6,
				CALCULATE( CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] < 6
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] < 6 &&
					ServLine[Resolution Code] = "COMPLETE"
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 7, 
			ServLine[Resolution Code] = "COMPLETE" 
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 5
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 7,
				CALCULATE( CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 5
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 5 &&
					ServLine[Resolution Code] = "COMPLETE"
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 8, 
			ServLine[Resolution Code] = "COMPLETE" 
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 7
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 8,
				CALCULATE( CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 7
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 7 &&
					ServLine[Resolution Code] = "COMPLETE"
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 9, 
			ServLine[Resolution Code] = "COMPLETE" 
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 6
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 9,
				CALCULATE( CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 6
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 6 &&
					ServLine[Resolution Code] = "COMPLETE"
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 10, 
			ServLine[Resolution Code] = "COMPLETE" 
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] < 10
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 10, 
				CALCULATE(
					MAX(ServLine[Dept Order Index]),
					FILTER(ServLine,
						ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
						ServLine[Dept Order Index] > 5 &&
						ServLine[Dept Order Index] < 10
					)
				) <> 9
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					AND (ServLine[Dept Order Index] > 5, ServLine[Dept Order Index] < 10) &&
					ServLine[Resolution Code] = "COMPLETE"
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			),
			IF ( 
				AND (
					ServLine[Dept Order Index] = 10,
					CALCULATE (
						CONTAINS ( ServLine, ServLine[Resolution Code],"COMPLETE"),
						FILTER ( ServLine,
							ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
							ServLine[Dept Order Index] = 9
						)
					)
				),
			VAR Maxresolveddate =
				CALCULATE (
					MAX ( ServLine[Resolved Date] ),
					FILTER ( ServLine,
						ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
						ServLine[Dept Order Index] = 9
					)
				)
			RETURN
				DATEDIFF (
					Maxresolveddate,
					ServLine[Resolved Date],
					DAY
				)
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 11, 
			ServLine[Resolution Code] = "COMPLETE" 
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 10 &&
				ServLine[Resolution Code] = "COMPLETE"
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 11, 
				CALCULATE( 
					CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 10
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 10
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	),

	IF ( 
		AND ( 
			ServLine[Dept Order Index] = 12, 
			ServLine[Resolution Code] = "COMPLETE" 
		),
	VAR Maxresolveddate =
		CALCULATE (
			MAX ( ServLine[Resolved Date] ),
			FILTER ( ServLine,
				ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
				ServLine[Dept Order Index] = 11 &&
				ServLine[Resolution Code] = "COMPLETE"
			)
		)
	RETURN
		DATEDIFF (
			Maxresolveddate,
			ServLine[Resolved Date],
			DAY
		),
		IF ( 
			AND ( 
				ServLine[Dept Order Index] = 12, 
				CALCULATE( 
					CONTAINS(ServLine,ServLine[Resolution Code],"COMPLETE"),
					FILTER( ServLine,
						ServLine[Document No_] = EARLIER( ServLine[Document No_] ) &&
						ServLine[Dept Order Index] = 11
					)
				)
			),
		VAR Maxresolveddate =
			CALCULATE (
				MAX ( ServLine[Resolved Date] ),
				FILTER ( ServLine,
					ServLine[Document No_] = EARLIER(ServLine[Document No_]) &&
					ServLine[Dept Order Index] = 11
				)
			)
		RETURN
			DATEDIFF (
				Maxresolveddate,
				TODAY(),
				DAY
			)
		)
	)
)
2 REPLIES 2
tharveysa
Frequent Visitor

Here is sample of lines and Results after:

 

 

Document No_Line No_Customer No_TypeNo_Location CodeDescriptionQuantityShortcut Dimension 1 CodeOrder DateResolution CodeResolved DateResolved UserNameDeptPosting DateOrderIndexDept Order IndexInvoice No_Needed by DateTT TotalCol Teardown (1)Col Blast (2)Col Hardware (3)Col Disk (4)Col NDT (5)Col Quote (6)Col BGT (7)Col Paint (8)Col Re-Pad (9)Col Final Assembly (10)Col Func Test (11)Col QA (12)Col Days 2 QuoteCol Total TT
137809210000623521 final assembly wheel ovh (no disk)1OMAHA8/26/2017COMPLETE9/19/2017IVILCHISFinal Assembly9/11/2017410320278/26/2017 0:0016         5  129
137809120000623521 teardown1OMAHA8/26/2017COMPLETE8/28/2017JFINOCHIAROTeardown9/11/201711320278/26/2017 0:00162           129
137809180000623521 quote work order1OMAHA8/26/2017COMPLETE9/7/2017MWOROBECQuote9/11/201736320278/26/2017 0:0016     7      129
137809160000623521 ndt main component1OMAHA8/26/2017COMPLETE8/30/2017NKERKMANNDT9/11/201725320278/26/2017 0:0016    0       129
137809130000623521 inspect & assemble hardware1OMAHA8/26/2017COMPLETE8/31/2017JADAMSHardware9/11/201723320278/26/2017 0:0016  3         129
137809220000623521 functional test wheel ovh (no disk)1OMAHA8/26/2017COMPLETE9/20/2017JSIMPSONFunction Testing9/11/2017511320278/26/2017 0:0016          1 129
137809230000623521 final ins & mntc release wheel ovh (no disk)1OMAHA8/26/2017COMPLETE9/20/2017JSIMPSONQA Check9/11/2017612320278/26/2017 0:0016           0129
137809200000623521 corosion control1OMAHA8/26/2017COMPLETE9/11/2017KPALMERTONBlend Glass Treat9/11/201707320278/26/2017 0:0016      12     129
137809190000623521 paint1OMAHA8/26/2017COMPLETE9/14/2017NCHALEKPaint9/11/201708320278/26/2017 0:0016       3    129
137809170000623521 clean & strip coatings1OMAHA8/26/2017COMPLETE8/30/2017BRANDONEBlast9/11/201702320278/26/2017 0:0016 2          129
137809140000623521 disassemble & clean hardware1OMAHA8/26/2017COMPLETE8/31/2017JADAMSHardware9/11/201703320278/26/2017 0:0016  3         129
137809150000623521 ndt all hardware1OMAHA8/26/2017COMPLETE8/31/2017NKERKMANHardware9/11/201703320278/26/2017 0:0016  3         129


This is the info I'm after: "Turn Times by Dept" 

 

137809Final Assembly

5

137809Teardown2
137809Quote7
137809NDT0
137809Hardware3
137809Function Testing1
137809QA Check0
137809Blend Glass Treat12
137809Paint3
137809Blast2
137809Hardware3

 

Column 3 above is the "Col..." combined from above according to each of the Depts.  If multiple dept results, we calculate the MAX of each and use this value.

HI @tharveysa,

 

It will be help if you share a pbix file to test.(your sample data columns are mixed and hard to analysis)

 

>>I have multiple measured columns I'm trying to combine into one column. 

For merge columns, I'd like to suggest you use enter to query editor to use unpivot column feature merge multiple columns. I think calculate with these merged columns should be simply than current formula.

 

Reference:

Power Query Unpivot Scenarios

 

BTW, your complex formula will also caused performance issue.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.