Forum Discussion
Solved
create a table from master table using DAX
I have the Master table by name "h1_daily"as follows
| Insertion Number | Month | Date | WEEK NO | SHIFT | GROSS OPERATING TIME | ITEM CODE | Status | MACHINE | NET OPERATING TIME | AVAILABLE TIME | EXPECTED OP | Unit | Cavities | Equipment failure loss | Setup & Adjustment loss | Cutter & Tool change loss | Startup loss |
| PS 000001 | Sep-24 | 2-Sep-2024 | Week 23 | A | 420.00 | SD00126 | BLANK | SSP-100T-01 | 395 | 242 | 29040 | NOS | 2 | 0 | 10 | 10 | 0 |
| PS 000002 | Sep-24 | 2-Sep-2024 | Week 23 | A | 90.00 | SD00210 | BLANK | SSP-100T-01 | 75 | 40 | 3200 | NOS | 2 | 0 | 35 | 0 | 0 |
| PS 000003 | Sep-24 | 2-Sep-2024 | Week 23 | B | 510.00 | SD00146 | BLANK | SSP-63T-02 | 470 | 250 | 12500 | NOS | 1 | 0 | 30 | 35 | 0 |
| PS 000004 | Sep-24 | 2-Sep-2024 | Week 23 | A | 90.00 | PA00208 | BLANK | SSP-63T-01 | 60 | 60 | 6000 | NOS | 2 | 0 | 0 | 0 | 0 |
| PS 000005 | Sep-24 | 2-Sep-2024 | Week 23 | A | 70.00 | PA00210 | BLANK | SSP-63T-01 | 70 | 66 | 6600 | NOS | 2 | 0 | 0 | 4 | 0 |
| PS 000006 | Sep-24 | 2-Sep-2024 | Week 23 | A | 290.00 | PA00694 | BLANK | SSP-63T-01 | 250 | 165 | 9900 | NOS | 1 | 0 | 25 | 0 | 0 |
Colums highted in blue colours is required in new table as follows
| MACHINE | Total losses | Total time |
| SSP-100T-01 | Equipment failure loss | 0 |
| SSP-100T-01 | Setup & Adjustment loss | 45 |
| SSP-100T-01 | Cutter & Tool change loss | 10 |
| SSP-100T-01 | Startup loss | 0 |
| SSP-63T-02 | Equipment failure loss | 0 |
| SSP-63T-02 | Setup & Adjustment loss | 30 |
| SSP-63T-02 | Cutter & Tool change loss | 35 |
| SSP-63T-02 | Startup loss | 0 |
| SSP-63T-01 | Equipment failure loss | 0 |
| SSP-63T-01 | Setup & Adjustment loss | 25 |
| SSP-63T-01 | Cutter & Tool change loss | 4 |
| SSP-63T-01 | Startup loss | 0 |
I have used dax query as follows
Measure 1 = union(SELECTCOLUMNS(H1_daily,"MACHINE",H1_daily[MACHINE],"Total time",H1_daily[CLITA],"Total losses","Clita"),
(SELECTCOLUMNS(H1_daily,"MACHINE",H1_daily[MACHINE],"Total time",H1_daily[Cutter & Tool change loss],"Total losses","Cutter & Tool change loss]")))
but givin error
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Can any one help?
Hi Vish_korg ,
You can achieve your desired output by using the 'Unpivot Other Columns' function in Power Query. I’ve attached an example pbix file for your reference.
Best regards,
2 Replies
Hi Vish_korg ,
You can achieve your desired output by using the 'Unpivot Other Columns' function in Power Query. I’ve attached an example pbix file for your reference.
Best regards,
I have done this by unpivoting and well as in power query.But I require this in DAX if possible.In power query I find it difficult for to link data to main table to get required output in dashboard.
