topic Re: DAX Fomula for Combining Multiple Columns? in DAX Commands and Tips

DAX Fomula for Combining Multiple Columns?

Anonymous — Thu, 27 Jul 2023 00:54:29 GMT

So I have a database with several thousand rows, with each row representing a unique architectural project, and our approach to recording the types of projects is to place an 'X' in one or multiple columns for that project's row for "Renovation", "Addition" and "New Construction" (many projects will fall into 2 categories.) I'm struggling to visualize how to covert these X's into a single new column that I can use to create a dropdown slicer to filter a table based on the selected project types. Do I need to Transpose or Pivot or would a single calculated value approach be sufficient? Any help would be appreciated.

Thanks.

Re: DAX Fomula for Combining Multiple Columns?

djallarii — Thu, 27 Jul 2023 02:22:52 GMT

The option you are looking for is in the Power Query editor which can be opened in either Power BI or Excel. I am assuming you know how to get to this point but if you need help, let me know.

Once your data is loaded into Power Query, select the last 3 columns of your data, click on the Transform tab, and find the Unpivot Columns tool.

This will change the layout of your dataset to 4 total columns. Two containing Project # and Project Name and two new columns called Attributes, with your project types, and Values, with the Xs.

Here's more info - https://support.microsoft.com/en-gb/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

Re: DAX Fomula for Combining Multiple Columns?

Anonymous — Fri, 28 Jul 2023 00:41:13 GMT

Thanks for the quick response and for the link to better understand the unpivot tool. I assume from there I should use a calculated column with an "IF" function to place the desired values "Renovation", "Addition", and "New Construction" in this new column that will become my dropdown slicer. I am new to DAX, but does that approach make sense?

Thanks again.

Re: DAX Fomula for Combining Multiple Columns?

djallarii — Fri, 28 Jul 2023 14:18:30 GMT

Based on the image you provided and the description of what you're trying to do, there's actually no DAX needed at all.

The steps below are assuming the source data is an Excel document formatted as indicated in the image you provided.

If the source data is in a true database, then the first few steps may be a bit different.

Step 1: Open Power BI, select Import data from Excel, and navigate to and select the source file.

Step 2: Select the source table from the options on the left and then click Transform Data. This will open the Power Query Editor

Step 3: Hold CTRL and click column header Renovation, Addition, New Construction

Step 4: Click the Transform tab on the tool ribbon and then click Unpivot Columns

Step 5: Double-click the Attribute column to rename it accordingly

Step 6: Double-click the Value column to rename it accordingly, though once the data is unpivoted, this field is no longer necessary and can actually be removed.

Step 7: Click Home on the tool ribbon and selected Close & Apply

Now your data is formatted for your described use. You can add Project #, Project Name, and Project Type to the columns of a table, and add Project Type to a slicer.