Calculate difference between two values using strings

[Anonymous]

Hi there,

I am trying to calculate the difference between two scores, one is before (Pre) the learning, another is after (Post).

We have a field called QuestionCategory which contains the product-questiontype_detail.  An example would be: 'Maths-Confidence_Pre' this example shows the confidence (a whole number) a learner had in maths before the learning.

A similar row will be 'Maths-Confidence_Post' to show the confidence of the learner after completion.

I would like to calculate the difference between the two confidence scores based on the product.

Learner ID	Question Category	Question	Score (1-7)	Desired Result: Confidence Change	Notes
1	Maths-Confidence_Pre	Before you start learning, how confident are you in the subject?	3	2	The confidence before the course is 3 and after 5, giving a difference of 2
1	Maths-Confidence_Post	Now you have finished, how confident are you in the subject?	5	2	The confidence before the course is 3 and after 5, giving a difference of 2
2	Maths-Confidence_Pre	Before you start learning, how confident are you in the subject?	2	4	The confidence before the course is 2 and after 6, giving a difference of 4
2	Maths-Confidence_Post	Now you have finished, how confident are you in the subject?	6	4	The confidence before the course is 2 and after 6, giving a difference of 4
3	Maths-Confidence_Pre	Before you start learning, how confident are you in the subject?	2		Only have a confidence result for before learning.  May not have finished yet or didn't complete questions.
5	English-Confidence_Pre	Before you start learning, how confident are you in the subject?	4	-1	The confidence before the course is 4 and after 3, giving a difference of -1 (representing a decrease in confidence)
5	English-Confidence_Post	Now you have finished, how confident are you in the subject?	3	-1	The confidence before the course is 4 and after 3, giving a difference of -1 (representing a decrease in confidence)
7	English-Confidence_Post	Now you have finished, how confident are you in the subject?	6		Only have a confidence result for after learning.  May not have answered initial questions.

This is an example set of data for the problem.  Ideally we would like to have a column like 'Desired Result: Confidence Change'

I've tried many things, but I'm pretty new to Power BI 🙂 any help is greatly appreciated!

I couldn't workout how to upload a pbix

[Anonymous]

Hi @Anonymous,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , The best way to have duplicate this column and split that into 2 column and use.

Or have measures like

Pre Marks= Calculate(Sum(Table[Marks]), filter(Table, search("Pre", Table[Exam],,0) >0) )

Psot Marks= Calculate(Sum(Table[Marks]), filter(Table, search("Post", Table[Exam],,0) >0) )

[Anonymous]

Hi @amitchandak thanks for your quick response!
I've tried creating the columns but I think I'm getting the syntax mixed up with Tableau (we recently switched)

Pre = CONTAINSSTRING(Table[QuestionCategory], "Pre") then Table[Score]

But this is giving me true/false