I’m trying to determine if there is a way and/or if it would be beneficial to try and merge the columns I need onto my Master Item table by using DAX in a New Table or if I can only do what I am looking for by way of Query Editor? I also was not sure if M Coding can be used in the New Table capacity or if that is strictly within Advanced Editor.
My Master Item table has columns of:
Item# // M1 // M2 // M3 // M4 // M5 // M6 // M7 // P1 // P2 // P3 // P4 // P5 // P6
Our SQL Server using a separate data table for each of those classifiers like:
M1 ID // M1 Description
and some have 1 extra column for a Value like:
M2 ID // M2 Description // M2 Value
Is there some way to use CrossJoin, NaturalInnerJoin, AddColumns, SelectColumns, Related, or other DAX to try and do with just through the NEW TABLE function? I am looking to avoid having Query Editor process through 13 Merges and 13 ExpandColumns on a table of over 1,000 Item #'s to put all of this data onto 1 simple table. I was starting to play around with CrossJoin but wasn’t sure if this was ideal. If CrossJoin works but leaves behind the original M1/P1 columns, can DAX be used to Remove those columns or can that only be done through Query Editor steps? (which wouldn’t be possible on the New Table function since those tables don’t appear in your Query Editor) Am I correct in all of these thoughts?
I do understand this isn’t the ideal way since I would be adding those columns of data down a large table versus leaving them separate, but I also have a rather complicated and diverse model due to are multiple tables on our SQL Server for a full supply chain company operating from raw material, to manufacturing, to distribution, sales, and also involving 3rd Party Logistics partners.
My reasoning for this is to have all potential SKU Classifications I would use for Item Sorting and Filtering in 1 location and not have to us 13 separate Tables for searching and dragging into reports and filters.