Forum Discussion
Create comparison on same data source
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...
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
10 Replies
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.
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....
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" )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?
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
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?you are welcome & no need to link
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.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.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.
