create a table from master table using DAX

[Vish_korg]

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?

[DataNinja777 · Accepted solution]

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,

[Vish_korg]

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.