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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

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.