Create a DAX table and measure

[kimchizal]

Goal is to have a measure calculating a percentage, but I first need help with setting up the data.

I'm lost trying to create the DAX measure that calculates "the percentage of people who grew by 1 point from pre to post" in my data. The data has the following granularity:

SiteID | TimeID | LabelID              | PreOrPost | Name | Score
1234   | 2          | Communication | Pre           | Alice    | 3
1234   | 2          | Communication | Post          | Alice   | 4
1234   | 2          | Communication | Pre            | Bob    | 1
1234   | 2          | Communication | Post          | Bob    | 1
1234   | 2          | Communication | Pre            | Terry   | 4
1234   | 2          | Communication | Post          | Terry   | 4
1234   | 2          | Communication | Pre           | Alice   | 1
1234   | 2          | Communication | Post          | Alice   | 4
1234   | 2          | Communication | Pre           | Terry   | 2
1234   | 2          | Communication | Post          | Terry   | 5

So as you can see, there are records for each person within each group of SiteID, TimeID, and LabelID for a `Pre` measurement and `Post` measurement. I'm trying to find the number of people who grew 1 or more points from pre to post.

For Communication, only Alice grew by 1 or more points. For Thoughtfulness though, both Alice and Terry grew by 1 or more. So the expected output of the previous table would be along the lines of:

LabelID              | Percentage
Communication | 33.33%
Thoughtfulness  | 100%

This didn't seem plausible with just a measure or two, so I've been trying to generate a table that does a self-join but that's been hard for me to setup. I can't even get a proper table `Generate`d. This is what I've tried but with errors:

= GENERATE(Table1, SELECTCOLUMNS(FILTER(Table1, Table1[Pre or Post] = "Post"),
Table1[SiteID ],
Table1[TimeID ],
Table1[LabelID ],
Table1[Pre or Post],
Table1[Name],
Table1[Score]
)
)
but that doesn't even work...

The table would probably have to do some sort of self-join on siteid, TimeID, and LabelID and Left.PreorPost = Pre and Right.PreorPost = Post. That way, I can get both the Pre and Post scores in 1 row.

Would love help or at least some guidance as to how to get the table setup. After that, I should be able to get a measure figured out.

[Anonymous · Accepted solution]

@kimchizal  - Try the following measure:

% Improved = 
var pre1 = SELECTCOLUMNS(FILTER(Table1,Table1[PreOrPost]="Pre"),"LabelIDAndName",Table1[LabelID] & Table1[Name], "Pre", [Score])
var post2 = SELECTCOLUMNS(FILTER(Table1,Table1[PreOrPost]="Post"),"LabelIDAndName",Table1[LabelID] & Table1[Name], "Post", [Score])
var combined = NATURALINNERJOIN(pre1,post2)
var change = ADDCOLUMNS(combined,"Change",[Post]-[Pre])
return DIVIDE(
    COUNTROWS(FILTER(change,[Change]>0)),
    COUNTROWS(change)
)

You could use the split into a Calculated Table and a Measure - the "Return" portion would be the measure.

You could also Pivot the table in Power Query - Pivot on the PreOrPost column, with Score as the Pivoted column.

Hope this helps,

Nathan