Forum Discussion
Create comparison on same data source
- 1 year ago
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
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
- AdiSarah551 year agoNew Member
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?- kushanNa1 year agoSuper User
you are welcome & no need to link