topic Re: Creating new table with Calculated Columns in DAX Commands and Tips

Creating new table with Calculated Columns

YonaD — Sat, 26 Feb 2022 00:16:31 GMT

Hi,

I had not much experience with DAX and I would like to ask some help with this situation.

I have a table below:

Table A

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

I would like to create a new table that will look like this:

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)

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?

Thank you in advanced for your help.

Re: Creating new table with Calculated Columns

VahidDM — Sat, 26 Feb 2022 04:56:41 GMT

Hi YonaD

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/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Creating new table with Calculated Columns

YonaD — Sat, 26 Feb 2022 15:40:52 GMT

Thank you. I will do that shortly.

Re: Creating new table with Calculated Columns

YonaD — Sat, 26 Feb 2022 15:53:28 GMT

In response to VahidDM, here's my question with more details in text format. Thank you!

I have a table below:

Table A

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

I would like to create a new table that will look like this:

Table B

Project Name Type Deliverable Duration
Project A Large XX datediff(finish date of RK-1 and finish date of RK-2
Project A Large YY datediff(finish date of RK-3 and finish date of RK-4
Project B Med XX datediff(finish date of RK-5 and finish date of RK-2

Project B Med YY datediff(finish date of RK-6 and finish date of RK-4
Project C Small XX datediff(finish date of RK-2 and finish date of RK-3
Project C Small YY datediff(finish date of RK-3 and finish date of RK-8

Thank you in advanced for your help.