[DAX] - finding pairs of rows and subtracting from each other based on a key.

[al4x]

Tearing my hair out,  just cannot seem to understand DAX!

First my data is in long format in form

Catagory1, Catagory2, Catagory3,....Catagory#, YEAR, SCORE

Where all the 'Category' columns are character based, YEAR is INT column for example '2016' and SCORE is an INT column from 0-100

i want to create a measure that for any filter set for the catefory columns the SCORE year=2018 is subtracted from the year=2016

and further average the result

a unique set of 'Category' columns forms the primary key to define this pairing

NOTE, not all 2016 results have a 2018 result. also, there will be 2018 rows that do not have a 2016 result,  In this case, i would want to exclude these completely.

Effctively a subset looking like

AAA,BBB,2016,100

AAA,BBB,2018,50

AAA,CCC,2016,75

AAA,CCC,2018,100

BBB,CCC,2016,98

would produce a calc of

((50-100)+(100-75))/2

Because i come from an SQL backgroud, i can do this easily in SQL and just load a new dataset in, but

1..to help my understanding of DAX

and

2..to stop my pbi file becomeing large

i want to do it in DAX

What i want to do is subtract

[v-chuncz-msft]

@al4x,

By the way, you may also select Pivot Column in Query Editor first.

[Stachu · Accepted solution]

this will work

Measure =
VAR SummaryPerYear =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Category1], 'Table'[Category2] ),
        "2016", CALCULATE ( SUM ( 'Table'[Score] ), 'Table'[Year] = 2016 ),
        "2018", CALCULATE ( SUM ( 'Table'[Score] ), 'Table'[Year] = 2018 )
    )
VAR ExcludeNull =
    FILTER ( SummaryPerYear, [2016] <> BLANK() && [2018] <> BLANK() )
RETURN
    AVERAGEX ( ExcludeNull, [2018] - [2016] )

and this is a good starting point for learning DAX
https://www.powerpivotpro.com/wp-content/uploads/2015/10/PowerPivotPro-Reference-Card.pdf

[al4x]

thanks for this.

It wasnt a simple answer for a beginner at least!

i will try to get my head round it and test it now. thank you!

if i understand this correctly it does look very similar to what i would do in SQL.