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

     

     

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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

    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....