Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ) ) ) )
Here is sample of lines and Results after:
Document No_ | Line No_ | Customer No_ | Type | No_ | Location Code | Description | Quantity | Shortcut Dimension 1 Code | Order Date | Resolution Code | Resolved Date | Resolved UserName | Dept | Posting Date | OrderIndex | Dept Order Index | Invoice No_ | Needed by Date | TT Total | Col 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 Quote | Col Total TT |
137809 | 210000 | 6235 | 2 | 1 | final assembly wheel ovh (no disk) | 1 | OMAHA | 8/26/2017 | COMPLETE | 9/19/2017 | IVILCHIS | Final Assembly | 9/11/2017 | 4 | 10 | 32027 | 8/26/2017 0:00 | 16 | 5 | 12 | 9 | ||||||||||||
137809 | 120000 | 6235 | 2 | 1 | teardown | 1 | OMAHA | 8/26/2017 | COMPLETE | 8/28/2017 | JFINOCHIARO | Teardown | 9/11/2017 | 1 | 1 | 32027 | 8/26/2017 0:00 | 16 | 2 | 12 | 9 | ||||||||||||
137809 | 180000 | 6235 | 2 | 1 | quote work order | 1 | OMAHA | 8/26/2017 | COMPLETE | 9/7/2017 | MWOROBEC | Quote | 9/11/2017 | 3 | 6 | 32027 | 8/26/2017 0:00 | 16 | 7 | 12 | 9 | ||||||||||||
137809 | 160000 | 6235 | 2 | 1 | ndt main component | 1 | OMAHA | 8/26/2017 | COMPLETE | 8/30/2017 | NKERKMAN | NDT | 9/11/2017 | 2 | 5 | 32027 | 8/26/2017 0:00 | 16 | 0 | 12 | 9 | ||||||||||||
137809 | 130000 | 6235 | 2 | 1 | inspect & assemble hardware | 1 | OMAHA | 8/26/2017 | COMPLETE | 8/31/2017 | JADAMS | Hardware | 9/11/2017 | 2 | 3 | 32027 | 8/26/2017 0:00 | 16 | 3 | 12 | 9 | ||||||||||||
137809 | 220000 | 6235 | 2 | 1 | functional test wheel ovh (no disk) | 1 | OMAHA | 8/26/2017 | COMPLETE | 9/20/2017 | JSIMPSON | Function Testing | 9/11/2017 | 5 | 11 | 32027 | 8/26/2017 0:00 | 16 | 1 | 12 | 9 | ||||||||||||
137809 | 230000 | 6235 | 2 | 1 | final ins & mntc release wheel ovh (no disk) | 1 | OMAHA | 8/26/2017 | COMPLETE | 9/20/2017 | JSIMPSON | QA Check | 9/11/2017 | 6 | 12 | 32027 | 8/26/2017 0:00 | 16 | 0 | 12 | 9 | ||||||||||||
137809 | 200000 | 6235 | 2 | 1 | corosion control | 1 | OMAHA | 8/26/2017 | COMPLETE | 9/11/2017 | KPALMERTON | Blend Glass Treat | 9/11/2017 | 0 | 7 | 32027 | 8/26/2017 0:00 | 16 | 12 | 12 | 9 | ||||||||||||
137809 | 190000 | 6235 | 2 | 1 | paint | 1 | OMAHA | 8/26/2017 | COMPLETE | 9/14/2017 | NCHALEK | Paint | 9/11/2017 | 0 | 8 | 32027 | 8/26/2017 0:00 | 16 | 3 | 12 | 9 | ||||||||||||
137809 | 170000 | 6235 | 2 | 1 | clean & strip coatings | 1 | OMAHA | 8/26/2017 | COMPLETE | 8/30/2017 | BRANDONE | Blast | 9/11/2017 | 0 | 2 | 32027 | 8/26/2017 0:00 | 16 | 2 | 12 | 9 | ||||||||||||
137809 | 140000 | 6235 | 2 | 1 | disassemble & clean hardware | 1 | OMAHA | 8/26/2017 | COMPLETE | 8/31/2017 | JADAMS | Hardware | 9/11/2017 | 0 | 3 | 32027 | 8/26/2017 0:00 | 16 | 3 | 12 | 9 | ||||||||||||
137809 | 150000 | 6235 | 2 | 1 | ndt all hardware | 1 | OMAHA | 8/26/2017 | COMPLETE | 8/31/2017 | NKERKMAN | Hardware | 9/11/2017 | 0 | 3 | 32027 | 8/26/2017 0:00 | 16 | 3 | 12 | 9 |
This is the info I'm after: "Turn Times by Dept"
137809 | Final Assembly | 5 |
137809 | Teardown | 2 |
137809 | Quote | 7 |
137809 | NDT | 0 |
137809 | Hardware | 3 |
137809 | Function Testing | 1 |
137809 | QA Check | 0 |
137809 | Blend Glass Treat | 12 |
137809 | Paint | 3 |
137809 | Blast | 2 |
137809 | Hardware | 3 |
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:
BTW, your complex formula will also caused performance issue.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |