Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YonaD
Frequent Visitor

Creating new table with Calculated Columns

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 NameTypeIdentifierFinish Date
Project ALargeRK-12022-01-01
Project ALargeRK-22022-01-31
Project ALargeRK-32022-03-15
Project ALargeRK-42022-03-28
Project BMedRK-52020-08-01
Project BMedRK-22020-09-01
Project BMedRK-62020-12-31
Project BMedRK-42021-01-15
Project CSmallRK-22021-08-06
Project CSmallRK-32021-09-07
Project CSmallRK-72021-10-11

 

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

Project NameTypeDeliverableDuration
Project ALargeXXdatediff(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 ALargeYYdatediff(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 BMedXXdatediff(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 BMedYYdatediff(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 CSmallXXdatediff(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 CSmallYYdatediff(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.

3 REPLIES 3
YonaD
Frequent Visitor

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

 

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.

 

VahidDM
Super User
Super User

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/

YonaD
Frequent Visitor

Thank you. I will do that shortly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors