Creating new table with Calculated Columns

Question

Hi,&nbsp;I had not much experience with DAX and I would like to ask some help with this situation.I have a table below:Table AProject NameTypeIdentifierFinish DateProject ALargeRK-12022-01-01Project ALargeRK-22022-01-31Project ALargeRK-32022-03-15Project ALargeRK-42022-03-28Project BMedRK-52020-08-01Project BMedRK-22020-09-01Project BMedRK-62020-12-31Project BMedRK-42021-01-15Project CSmallRK-22021-08-06Project CSmallRK-32021-09-07Project CSmallRK-72021-10-11&nbsp;I would like to create a new table that will look like this:Project NameTypeDeliverableDurationProject ALargeXXdatediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-1",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-2",Table A[Finish Date]),Day)Project ALargeYYdatediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-3",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-4",Table A[Finish Date]),Day)Project BMedXXdatediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-5",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-2",Table A[Finish Date]),Day)Project BMedYYdatediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-6",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-4",Table A[Finish Date]),Day)Project CSmallXXdatediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-2",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-3",Table A[Finish Date]),Day)Project CSmallYYdatediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-3",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] &amp;&amp; Table A [Identifier] = "RK-8",Table A[Finish Date]),Day)&nbsp;I have created separate DAX tables for each deliverable and combined all those dax tables together. But is there a way where I can combine all of these in one table at once without creating multiple tables?&nbsp;Thank you in advanced for your help.

vahiddm · Answer

Hi&nbsp;YonaD&nbsp;&nbsp;Can you post sample data as text and expected output?Not enough information to go on;please see this post regarding How to Get Your Question Answered Quickly:https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.4. Relation between your tablesAppreciate your Kudos!!LinkedIn:www.linkedin.com/in/vahid-dm/

yonad · Answer

Thank you. I will do that shortly.

yonad · Answer

In response to&nbsp;VahidDM, here's my question with more details in text format. Thank you!I have a table below:Table AProject Name&nbsp; &nbsp;Type&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Identifier&nbsp; &nbsp; &nbsp; &nbsp; Finish DateProject A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Large&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2022-01-01Project A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Large&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2022-01-31Project A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Large&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2022-03-15Project A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Large&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2022-03-28Project B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Med&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2020-08-01Project B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Med&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2020-09-01Project B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Med&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2020-12-31Project B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Med&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2021-01-15Project C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Small&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2021-08-06Project C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Small&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2021-09-07Project C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Small&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RK-7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2021-10-11&nbsp;I would like to create a new table that will look like this:&nbsp;Table BProject Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Type&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Deliverable&nbsp; &nbsp; &nbsp; &nbsp; DurationProject A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Large&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;datediff(finish date of RK-1 and finish date of RK-2Project A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Large&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;datediff(finish date of RK-3 and finish date of RK-4Project B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Med&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;datediff(finish date of RK-5 and finish date of RK-2Project B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Med&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;datediff(finish date of RK-6 and finish date of RK-4Project C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Small&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; XX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;datediff(finish date of RK-2 and finish date of RK-3Project C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Small&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; YY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; datediff(finish date of RK-3 and finish date of RK-8&nbsp;I have created separate DAX tables for each deliverable and combined all those dax tables together. But is there a way where I can combine all of these in one table at once without creating multiple tables?&nbsp;Thank you in advanced for your help.&nbsp;

Forum Discussion

Creating new table with Calculated Columns

3 Replies

Related content

TMDL - calculated column

Semantic model - create calculated column

Create columns group

Direct Lake Model - Calculated Column/Tables

Create calculated Rank column in Powerbi

Project Name	Type	Identifier	Finish Date
Project A	Large	RK-1	2022-01-01
Project A	Large	RK-2	2022-01-31
Project A	Large	RK-3	2022-03-15
Project A	Large	RK-4	2022-03-28
Project B	Med	RK-5	2020-08-01
Project B	Med	RK-2	2020-09-01
Project B	Med	RK-6	2020-12-31
Project B	Med	RK-4	2021-01-15
Project C	Small	RK-2	2021-08-06
Project C	Small	RK-3	2021-09-07
Project C	Small	RK-7	2021-10-11

Project Name	Type	Deliverable	Duration
Project A	Large	XX	datediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-1",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-2",Table A[Finish Date]),Day)
Project A	Large	YY	datediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-3",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-4",Table A[Finish Date]),Day)
Project B	Med	XX	datediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-5",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-2",Table A[Finish Date]),Day)
Project B	Med	YY	datediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-6",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-4",Table A[Finish Date]),Day)
Project C	Small	XX	datediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-2",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-3",Table A[Finish Date]),Day)
Project C	Small	YY	datediff(minx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-3",Table A[Finish Date]),maxx(filter(Table A, Table[Project Name]=Table B[Project Name] && Table A [Identifier] = "RK-8",Table A[Finish Date]),Day)