Forum Discussion

AdiSarah55's avatar
AdiSarah55
New Member
1 year ago
Solved

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 ...
Need Help
  • kushanNa's avatar
    kushanNa
    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

     

     

     

kushanNa's avatar
kushanNa
Super User
1 year ago

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's avatar
AdiSarah55
New Member
1 year ago

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?