Forum Discussion
Create a DAX table and measure
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 - 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
1 Reply
Anonymous - 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