cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

create new table with partial columns from another table in dax

Hi

I wonder if there is a way to create a table in dax that is the same like another table but with less colmun.

for example :

table "A" has "col1","col2","col3"

I want to create table "B" with just "col1","col2"

 

thanks

13 REPLIES 13
Advocate I
Advocate I

 I am not a pro power bi user. I think I find out an easy way to solve your problem if we are facing the same problem. 

TABLE B = 

SELECTCOLUMNS(CALCULATETABLE( TABLEA,FILTER(TABLEA,TABLEA[col1]=x)),"col1",'TABLEA'[col1],"col2",TABLEA[col2])
 
Consider the duplicate rows, you can wrap a "DISTINCT" on the formula as below"
 

TABLE B = 

DISTINCT(SELECTCOLUMNS(CALCULATETABLE( TABLEA,FILTER(TABLEA,TABLEA[col1]=x)),"col1",'TABLEA'[col1],"col2",TABLEA[col2]))
 
Frequent Visitor

I see what @SqlJason is saying but it seems a bit crazy to bring in two tables by query twice, once with one set of columns and once with another, call the second import of the tables SelectedColumns1 and SelectedColumns2

 

In my case I wanted to use EXCEPT to find rows in SelectedColumns1 not in SelectedColumns2; this requires the two tables have a) exactly the same number and type of columns, and b) the right columns to produce the required result i.e. no identity type columns

 

What I'd really like to be able to do is to use EXCEPT and create my SelectedColumns1 and SelectedColumns2 on the fly, as table expressions, which doesn't seem to be possible

 

In my case using CALCULATETABLE will remove some rows, as pointed out by @greggyb

 

I can't do what @Greg_Deckler says either as if you copy the table you get all columns and there's no way to hid columns, or rather if you do hide them, it affects only the reporting not the matching - I think

@JohnDawson - Don't copy the table, copy the query. Edit that query to remove the columns that you do not want. You now have 2 queries that create 2 tables, one with all the columns and one with fewer than all of the columns.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes @smoupre, that's what I've done - and indeed it seems the only way; I just thought there might be a more elegant solution. But thanks.

Resident Rockstar
Resident Rockstar

Any reason you have to do this in DAX?

 

There's not really a good way to do this in the general case. If 'Table'[Col1] and 'Table'[Col2] have no duplicates, you can use one of SUMMARIZE() as shown above or GROUPBY() with identical syntax, but both of these perform a logical group by operation. This means that if you have duplicates, your table will have fewer rows AND fewer columnns than the base.

 

As @Greg_Deckler, mentioned, duplicating the query in Power Query is likely a better option.

I agree with both @greggyb and @Greg_Deckler that this should be pushed to ETL if possible. If you are interested to learn why pushing to ETL or Power Query is a better option, feel free to go through the end portion of my post - http://sqljason.com/2015/09/my-thoughts-on-calculated-tables-in.html

I have a table with N Columns where I have (among others) the following columns and conditions
A) There are 3 executing/managing sites (IN-HOUSE)
B) There are 4 outsourcing sites (OFFSHORE)
C) For every offshore Project, there must always be an IN-HOUSEManaging site

1) DEPARTMENT - Contains Coding/Managing site (Either in-house or Offshore) - IN-HOUSE_A,IN-HOUSE_B, IN-HOUSE_C, OFFSHORE_A, OFFSHORE_B, OFFSHORE_C, OFFSHORE_D
2) Project_Name
3) Plan_Date

a) If the project is being Managed and Coded 100% in-house in one SINGLE SITE, then there is just ONE record for that PROJECT in that DEPARTMENT in a certain PLAN_DATE, e.g.: DEPARTMENT = IN-HOUSE_A - (B or C)
b) If the project is being Managed and Coded 100% in-house in more than one Executing Site, then there is ONE record for each Executing Site in the DEPARTMENT column for that PROJECT in a certain PLAN_DATE (in this case, it could be considered as separate projects since Budgeting is done at the Executing/Managing Site level)
c) If a Project is sent offshore (it can be sent to 1 or more off-shore Coding sites), there would be two or more records
i. One record with DEPARTMENT = "IN-HOUSE_A" -(B or C)
ii. One record with DEPARTMENT = "OFFSHORE_A" -(B, C or D) for each off-shore site where code is being done

I want to find a way in DAX (or Power Query) to add a CUSTOM COLUMN in that table to indicate the Managing Site for each outsourced project for easier reporting and filtering, e.g.:
a) Cost plan by Executing site for 100% managed projects
b) Cost Plan by Coding site for outsourced projects
c) Cost Plan by Managing Site-Coding Site

NOTES:
To detect Managing Site, I was thinking on locate DUPLICATES (PROJECT_DATE + PLAN_DATE) then:
i. locate the DEPARTMENT value for record containing DEPARTMENT = IN-HOUSE_X (A, B, or C) value
ii. Locate it's associated records (with same PROJECT_DATE + PLAN_DATE) but DEPARTMENT = OFFSHORE_X (A, B, or C) and copy the IN-HOUSE department from the previous record to the Custom column, understanding that can be 1 or more offshore records||
iii. For unique records, I'd add a custom label (e.g.: "100% IN-HOUSE") or leave it blank in that Custom column

I can't change the design on the table since it comes from a C.A. Clarity PPM feed

Super User IV
Super User IV

You could also copy the query that creates your 1st table, edit the query and remove the offending column from the query.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler I usually follow this steps; try not to bother with dax regarding minor stuffs; like duplicate table, drop the unnecessary column, if required, remove duplicates, then if required, add the relationship manually. Yet just wanted to refresh my memory with dax expressions... Smiley Very Happy

Try using SUMMARIZECOLUMNS ... 

I think selectcolumns() is what you are looking for:

 

https://msdn.microsoft.com/en-us/library/mt761759.aspx

Memorable Member
Memorable Member

In Power BI Desktop, you can create a new calculated table with the formula like shown below

 

NewTableName = SUMMARIZE(ExistingTableName, ExistingTableName[column1], ExistingTableName[column2])

IS it possible to add a where clause to this as well??

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors