Merging two Tables based on different values

[Anonymous]

Hello everyone,

I hope someone has an idea how I can solve this problem. Basically I have two tables which are related with each other over the ID.

Lets say I have folloing two tables:

For this example I have 3 Versions and I want one result for every version for every testcase. In reality I dont know the amount of Versions. I want the resulting table to look like this:

So the question is: Is there a way to do this, without knowing the amount of versions?

I want to achieve this, so in a stacked column chart I will have a constant height for every version.

P.S: I cant show real data because its company related.

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think you need to create a distinct table first to display all version, then create a measure to display the corresponding status.

Like this:

Table = DISTINCT(Table2[Version])

Measure =
VAR a =
    MAXX (
        FILTER (
            ALL ( Table2 ),
            [ID] = SELECTEDVALUE ( Table1[ID] )
                && [Version] = SELECTEDVALUE ( 'Table'[Version] )
        ),
        [Status]
    )
RETURN
    IF ( a = BLANK (), "null", a )

Note: Putting only two columns will report an error. After the measure is put in, the visual can be displayed normally.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[Anonymous]

Thank you very much for your well explained idea. I will have to try this later today and than I will get back to this solution.

[amitchandak]

@Anonymous , What is logic to get version .

In power query, you can append, but the version will null, you can use fill down

In dax you can add new column in table 1 first

version = max(Table2[version])+1

Status = blank()

Then create a new tbale

union(Table1, Table2)

[Anonymous]

@amitchandakThe logic of version is this. Every test gets tested again on every version or at least it should be. But a lot of times for several reasons they will not be tested on one version and tested again on the next version. Thats why in Result there will be only results for versions and testcases, that have been executed. But I want to create a table where even for those times a testcase didnt get tested on a version, there is still some result like null NA or blank. Instead the match doesnt exist, because Power Query doesnt really know for what version the testcase should have been tested. Thanks