Need help with a calculated table - full table A X filtered data from Table B, per row in A

[Draxxalon]

I'm struggling to generate a calculated table that merges some data from 2 different tables.

My dataset:

Table 1:

'Builds'

[Build#], [Build Status]

A, Pass

B, Fail

C, Pass
...

Table 2:
'Changelist'
[CL]

1

2

3

4

5
6
...

The values in Build# (A, B, C (etc)) will exist as a value in 'Changelist', although not as consecutive values.
ie:  BuildA = CL1, BuildB = CL3, C=CL6

What I want to generate is a calculated table that shows all of the CL's in each build:
'CLinBuilds'
[Build],[CL]
A, 1
B, 2
B, 3
C, 4
C, 5
C, 6
etc

There is currently no relationship between the tables (one of the intended uses of this table is to create said relationship).

What I'm currently trying is thus:
CLinBuilds = 
VAR SelectedBuild = SELECTEDVALUE(Builds[Build#])
VAR PriorBuild =
CALCULATE
(
    max(Builds[Build#]),
    Builds[Build#]<SelectedBuild
)
RETURN
GENERATE
(
  SUMMARIZE(Builds,Builds[Build#]),
  CALCULATETABLE
  (
    SUMMARIZE(ChangeList,ChangeList[CL]),
    FILTER
    (
    ChangeList,
    ChangeList[CL] <= SelectedBuild
    && ChangeList[CL] > PriorBuild
    )
  )
)


This returns nothing, and I'm pretty sure it's because I don't have a proper row context in the second table I'm building in that calculatetable... but I'm struggling to figure out how to fix that.

If I hard-code "SelectedBuild" to a build #, it will generate the correct information for that build, but I can't get it to do it for "all" the builds.

Any advice?

Thanks in advance!

[v-piga-msft]

Hi @Draxxalon,

Assuming that you have the two tables like below.

I'm a little confused about that the logic to achieve your expected output. To get the solution, could you describe your logic in more details?

Best Regards,

Cherry

[Draxxalon]

In the actual data, both columns are 7-digit numbers (I used letters + Numbers to disinguish difference, potentially introducing confusion)
Changes are submitted faster than Builds can be generated, so builds can have many changelists submitted between them.    
Each build is generated out of a set of CL's, with it's build# matching the highest CL it was generated from.
CL's are usually non-sequential (but ordered by size), and can have gaps of 1000+ between them.   My current data has size gaps between Build# of up to ~70k.

So, using numbers (to be less confusing) for both sets of data, we would have:
Build#, Status
1, Pass
3, Fail
6, Pass

CL
1

2

3

4

5

6

Results:
Build#, CL
1, 1
3, 2
2, 2
6, 4
6, 5
6, 6

Does that help clarify things @v-piga-msft?

[Draxxalon]

I have come up with a method that generates the correct values... but does not allow me to create the relationship between 'Builds' + 'Changelist' (due to circular dependencies).

It also takes a couple minutes to run, which isn't ideal.

Instead of trying to figure out the CL's contained in each build, try to figure out which build each CL is in.

Adding a calculated column to the CL table "inBuild"
inBuild =
CALCULATE
(
  MIN(Builds[Build#]),
  FILTER
  (

    Builds,

    Builds[Build#] >= Changelist[CL]
  )
)

(plus an exclusion filters to deal with really old CLs from before the system generated builds, which isn't particularly important)