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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Vish_korg
Frequent Visitor

create a table from master table using DAX

I have the Master table by name "h1_daily"as follows

Insertion NumberMonthDateWEEK NOSHIFTGROSS OPERATING TIMEITEM CODEStatusMACHINENET OPERATING TIME AVAILABLE TIME EXPECTED OPUnitCavitiesEquipment failure lossSetup & Adjustment lossCutter & Tool change lossStartup loss
PS 000001Sep-242-Sep-2024Week 23A420.00SD00126BLANKSSP-100T-0139524229040NOS2010100
PS 000002Sep-242-Sep-2024Week 23A90.00SD00210BLANKSSP-100T-0175403200NOS203500
PS 000003Sep-242-Sep-2024Week 23B510.00SD00146BLANKSSP-63T-0247025012500NOS1030350
PS 000004Sep-242-Sep-2024Week 23A90.00PA00208BLANKSSP-63T-0160606000NOS20000
PS 000005Sep-242-Sep-2024Week 23A70.00PA00210BLANKSSP-63T-0170666600NOS20040
PS 000006Sep-242-Sep-2024Week 23A290.00PA00694BLANKSSP-63T-012501659900NOS102500

Colums highted in blue colours is required in new table as follows

MACHINETotal lossesTotal time
SSP-100T-01Equipment failure loss0
SSP-100T-01Setup & Adjustment loss45
SSP-100T-01Cutter & Tool change loss10
SSP-100T-01Startup loss0
SSP-63T-02Equipment failure loss0
SSP-63T-02Setup & Adjustment loss30
SSP-63T-02Cutter & Tool change loss35
SSP-63T-02Startup loss0
SSP-63T-01Equipment failure loss0
SSP-63T-01Setup & Adjustment loss25
SSP-63T-01Cutter & Tool change loss4
SSP-63T-01Startup loss0

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?

 

 

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

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.

 

DataNinja777_0-1730386006787.png

 

 

Best regards,

 

 

View solution in original post

2 REPLIES 2
DataNinja777
Super User
Super User

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.

 

DataNinja777_0-1730386006787.png

 

 

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.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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