Create comparison on same data source

[AdiSarah55]

HI, 
i really need your support, i'm strugling with many AI progrmas but still couldn't find the good answer.
i have data table in powerbi looks like this:
hash per build, with all tests verdicts and actual

hash	build	test1 verdict	test1 actual	test2 verdict	test2 actual	test3 verdict	test3 actual
1234	1	pass	1	fail	4	pass	great
1234	2	pass	2	fail	5	pass	great
2345	1	pass	3	pass	6	pass	poor
2345	2	fail	4	pass	4	pass	great
3456	1	fail	4	pass	4	pass	poor
3456	2	pass	5	fail	4	fail	poor

i need to create 2 tables, 1 that present the tests that were changed between the 2 builds, and 1 that present the tests that were not changed:
not changed:

test	hash	verdict build1	verdict build2	actual build1	actual build2
test1	1234	pass	pass	1	2
test2	1234	fail	fail	4	5
test3	1234	pass	pass	great	great
test2	2345	pass	pass	6	4
test3	2345	pass	pass	poor	great

changed: 

test	hash	verdict build1	verdict build2	actual build1	actual build2
test1	2345	pass	fail	3	4
test1	3456	fail	pass	4	5
test2	3456	pass	fail	4	4
test3	3456	pass	fail	poor	poor

the purpose is to compare between 2 builds and see changes, so i giv 2 filters baseBuild and CompareBuild
now i need to take out the data per build.

is that even possible? I'm desperate 😞

the main issue is to filter by build number 2 different filters...

[v-pnaroju-msft]

Hi AdiSarah55,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi AdiSarah55,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi AdiSarah55,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[kushanNa · Accepted solution]

Hi @AdiSarah55 

follow this method

first create an unpivot table

unpivotnewtable = 
UNION(
    SELECTCOLUMNS(
        'YourTable', 
        "hash", 'YourTable'[hash], 
        "build", 'YourTable'[build], 
        "test", "test1", 
        "verdict", 'YourTable'[test1 verdict], 
        "actual", 'YourTable'[test1 actual]
    ),
    SELECTCOLUMNS(
        'YourTable', 
        "hash", 'YourTable'[hash], 
        "build", 'YourTable'[build], 
        "test", "test2", 
        "verdict", 'YourTable'[test2 verdict], 
        "actual", 'YourTable'[test2 actual]
    ),
    SELECTCOLUMNS(
        'YourTable', 
        "hash", 'YourTable'[hash], 
        "build", 'YourTable'[build], 
        "test", "test3", 
        "verdict", 'YourTable'[test3 verdict], 
        "actual", 'YourTable'[test3 actual]
    )
)

and then create two separate table for build picks 

first pick = DISTINCT(unpivotnewtable[build] )

second pick = DISTINCT(unpivotnewtable[build] )

and create this measure 

Change Check = 
VAR FirstPickBuild = SELECTEDVALUE('first pick'[build])
VAR SecondPickBuild = SELECTEDVALUE('second pick'[build])

VAR FirstPickVerdict =
    CALCULATE (
        MAX ( 'unpivotnewtable'[verdict] ),
        'unpivotnewtable'[build] = FirstPickBuild,
        ALLEXCEPT('unpivotnewtable', 'unpivotnewtable'[hash], 'unpivotnewtable'[test])
    )

VAR SecondPickVerdict =
    CALCULATE (
        MAX ( 'unpivotnewtable'[verdict] ),
        'unpivotnewtable'[build] = SecondPickBuild,
        ALLEXCEPT('unpivotnewtable', 'unpivotnewtable'[hash], 'unpivotnewtable'[test])
    )

RETURN
    IF ( FirstPickVerdict = SecondPickVerdict, "0", "1" )

and create two new tables in visual and add change check measure as a filter and for 1 table ask it to show 0 (not changed) and for other 1 (changed)

create two filter for the comparison between builds

[AdiSarah55]

Thanks again @kushanNa for your helpful support!!🤓 really appreciate! 
i see your example, and in the table you present we can see only build 1 & 2 
since your original table includes only builds 1 & 2 probably, i have many builds so i can't show in the table only the selected builds,,, 
BTW do i need to link the "first pick" and "second pick" tables to the unpivot table?

[kushanNa]

you are welcome & no need to link 

[kushanNa]

hi

first create a unpivoted table 

UnpivotedTableWithDuplicateCheck = 
ADDCOLUMNS (
    UNION(
        SELECTCOLUMNS(
            'YourTable', 
            "hash", 'YourTable'[hash], 
            "build", 'YourTable'[build], 
            "test", "test1", 
            "verdict", 'YourTable'[test1 verdict], 
            "actual", 'YourTable'[test1 actual]
        ),
        SELECTCOLUMNS(
            'YourTable', 
            "hash", 'YourTable'[hash], 
            "build", 'YourTable'[build], 
            "test", "test2", 
            "verdict", 'YourTable'[test2 verdict], 
            "actual", 'YourTable'[test2 actual]
        ),
        SELECTCOLUMNS(
            'YourTable', 
            "hash", 'YourTable'[hash], 
            "build", 'YourTable'[build], 
            "test", "test3", 
            "verdict", 'YourTable'[test3 verdict], 
            "actual", 'YourTable'[test3 actual]
        )
    ),
    "DuplicateCheck", 
    IF (
        COUNTROWS (
            FILTER (
                UNION(
                    SELECTCOLUMNS(
                        'YourTable', 
                        "hash", 'YourTable'[hash], 
                        "build", 'YourTable'[build], 
                        "test", "test1", 
                        "verdict", 'YourTable'[test1 verdict], 
                        "actual", 'YourTable'[test1 actual]
                    ),
                    SELECTCOLUMNS(
                        'YourTable', 
                        "hash", 'YourTable'[hash], 
                        "build", 'YourTable'[build], 
                        "test", "test2", 
                        "verdict", 'YourTable'[test2 verdict], 
                        "actual", 'YourTable'[test2 actual]
                    ),
                    SELECTCOLUMNS(
                        'YourTable', 
                        "hash", 'YourTable'[hash], 
                        "build", 'YourTable'[build], 
                        "test", "test3", 
                        "verdict", 'YourTable'[test3 verdict], 
                        "actual", 'YourTable'[test3 actual]
                    )
                ),
                [test] = EARLIER([test]) &&
                [hash] = EARLIER([hash]) &&
                [verdict] = EARLIER([verdict])
            )
        ) > 1, 
        "Not Changed", 
        "Changed"
    )
)

and create other two tables 

changed table

ChangedTable = 
FILTER (
    UnpivotedTableWithDuplicateCheck, 
    UnpivotedTableWithDuplicateCheck[DuplicateCheck] = "Changed"
)

not changed table

NotChangedTable = 
FILTER (
    UnpivotedTableWithDuplicateCheck, 
    UnpivotedTableWithDuplicateCheck[DuplicateCheck] = "Not Changed"
)

[AdiSarah55]

thanks @kushanNa for your help!
your answer is great if the build are known in advance, but as i mentioned, i want to give the user the option to choose which builds to compare (1 & 2 are examples, we have many results for several builds, and i want the user to select build1 and build2. is there an option to do it?

[bhanu_gautam]

 @AdiSarah55 

Create a calculated table for tests that were not changed:

Go to the "Modeling" tab and select "New Table".
Use the following DAX formula to create the table for tests that were not changed:

dax
NotChangedTests =
VAR BaseBuild = 1 -- Replace with your base build filter
VAR CompareBuild = 2 -- Replace with your compare build filter
RETURN
FILTER(
ADDCOLUMNS(
CROSSJOIN(
DISTINCT(SELECTCOLUMNS('Table', "Test", 'Table'[test1 verdict], 'Table'[test2 verdict], 'Table'[test3 verdict])),
DISTINCT(SELECTCOLUMNS('Table', "Hash", 'Table'[hash]))
),
"VerdictBuild1", LOOKUPVALUE('Table'[test1 verdict], 'Table'[hash], [Hash], 'Table'[build], BaseBuild),
"VerdictBuild2", LOOKUPVALUE('Table'[test1 verdict], 'Table'[hash], [Hash], 'Table'[build], CompareBuild),
"ActualBuild1", LOOKUPVALUE('Table'[test1 actual], 'Table'[hash], [Hash], 'Table'[build], BaseBuild),
"ActualBuild2", LOOKUPVALUE('Table'[test1 actual], 'Table'[hash], [Hash], 'Table'[build], CompareBuild)
),
[VerdictBuild1] = [VerdictBuild2] && [ActualBuild1] = [ActualBuild2]
)

Create a calculated table for tests that were changed:
Go to the "Modeling" tab and select "New Table".
Use the following DAX formula to create the table for tests that were changed:

dax
ChangedTests =
VAR BaseBuild = 1 -- Replace with your base build filter
VAR CompareBuild = 2 -- Replace with your compare build filter
RETURN
FILTER(
ADDCOLUMNS(
CROSSJOIN(
DISTINCT(SELECTCOLUMNS('Table', "Test", 'Table'[test1 verdict], 'Table'[test2 verdict], 'Table'[test3 verdict])),
DISTINCT(SELECTCOLUMNS('Table', "Hash", 'Table'[hash]))
),
"VerdictBuild1", LOOKUPVALUE('Table'[test1 verdict], 'Table'[hash], [Hash], 'Table'[build], BaseBuild),
"VerdictBuild2", LOOKUPVALUE('Table'[test1 verdict], 'Table'[hash], [Hash], 'Table'[build], CompareBuild),
"ActualBuild1", LOOKUPVALUE('Table'[test1 actual], 'Table'[hash], [Hash], 'Table'[build], BaseBuild),
"ActualBuild2", LOOKUPVALUE('Table'[test1 actual], 'Table'[hash], [Hash], 'Table'[build], CompareBuild)
),
[VerdictBuild1] <> [VerdictBuild2] || [ActualBuild1] <> [ActualBuild2]
)

Adjust the filters: Replace BaseBuild and CompareBuild with the actual build numbers you want to compare.

Visualize the tables: Use table visualizations to display the NotChangedTests and ChangedTests tables.

[AdiSarah55]

thanks @bhanu_gautam for your answer! the main issue is to distinguish between the 2 build filters, 
so when you are saying :
VAR BaseBuild = 1 -- Replace with your base build filter
VAR CompareBuild = 2 -- Replace with your compare build filter
what do u mean?
i have a table and build column there, there is no name for filters....