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
macinr
Helper I
Helper I

How to create sub table based on other table

Hi, 

I am syncrhonizing data from VSTS (Azure Devops) - Analytics View. 

 

I want to create a table based on last 30 days historical table which I fetch from Analytics plugin. 

 

I was able to build an iteration paths table by simply using: 

Interations Dim = DISTINCT('Work Items - Last 30 days'[Iteration Path])
However I would like to build a table, which consists of:
  • Distinct name of iteration path
  • Iteration start date
  • Iteration end date

I tried with summarize, but the problem is that there might be multiple values..

 

Ideally I would like to have a table based on dates in a range.

Date Range = CALENDAR(DATE(2019;01;01); DATE(2020;12;31))
which includes those fields above...
To be more precise, here are the data I need to transform...
 
Current table       
Iteration PathIteration Start DateIteration End DateTitleDate Effort completedEffort remaining
Iteration 101/01/201907/01/2019Task 102/01/2019 15
Iteration 101/01/201907/01/2019Task 202/01/2019 23
Iteration 101/01/201907/01/2019Task 104/01/2019 52
Iteration 101/01/201907/01/2019Task 204/01/2019 34
Iteration 101/01/201907/01/2019Task 305/01/2019 45
Iteration 208/01/201915/01/2019Task 608/01/2019 15
Iteration 208/01/201915/01/2019Task 408/01/2019 23
Iteration 208/01/201915/01/2019Task 808/01/2019 52
Iteration 208/01/201915/01/2019Task 910/01/2019 34
Iteration 208/01/201915/01/2019Task 910/01/2019 45
Iteration 316/01/201923/01/2019Task 1116/01/2019 15
Iteration 316/01/201923/01/2019Task 1216/01/2019 23
        
Target table       
Date Iteration Start DateIteration End Date    
01/01/2019Iteration 101/01/201907/01/2019    
02/01/2019Iteration 101/01/201907/01/2019    
03/01/2019Iteration 101/01/201907/01/2019    
04/01/2019Iteration 101/01/201907/01/2019    
05/01/2019Iteration 101/01/201907/01/2019    
06/01/2019Iteration 101/01/201907/01/2019    
07/01/2019Iteration 101/01/201907/01/2019    
08/01/2019Iteration 208/01/201915/01/2019    
09/01/2019Iteration 208/01/201915/01/2019    
10/01/2019Iteration 208/01/201915/01/2019    
11/01/2019Iteration 208/01/201915/01/2019    
12/01/2019Iteration 208/01/201915/01/2019    
13/01/2019Iteration 208/01/201915/01/2019    
14/01/2019Iteration 208/01/201915/01/2019    
15/01/2019Iteration 208/01/201915/01/2019    
16/01/2019Iteration 316/01/201923/01/2019    
 
Any support is greatly appreciated
All the best
Marcin 
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @macinr ,

You can try to use following calculate table formula to create a expand table with all detail date records:

New Table =
VAR _calendar =
    CALENDAR (
        MIN ( Table[Iteration Start Date] ),
        MAX ( Table[Iteration End Date] )
    )
RETURN
    FILTER (
        CROSSJOIN (
            SUMMARIZE ( Table, [Path], [Iteration Start Date], [Iteration End Date] ),
            _calendar
        ),
        [Date] >= [Iteration Start Date]
            && [Date] <= [Iteration End Date]
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
arsaveli
Helper I
Helper I

You can also get Iterations directly from odata, if you want complete list of iterations (not just iterations in your work items), using following query:

 

VSTS.Feed("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/Iterations")

v-shex-msft
Community Support
Community Support

Hi @macinr ,

You can try to use following calculate table formula to create a expand table with all detail date records:

New Table =
VAR _calendar =
    CALENDAR (
        MIN ( Table[Iteration Start Date] ),
        MAX ( Table[Iteration End Date] )
    )
RETURN
    FILTER (
        CROSSJOIN (
            SUMMARIZE ( Table, [Path], [Iteration Start Date], [Iteration End Date] ),
            _calendar
        ),
        [Date] >= [Iteration Start Date]
            && [Date] <= [Iteration End Date]
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for help @v-shex-msft ! This is exactly what I needed. 

 

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.