Comparing data from same column given some conditions

[PedroValmor]

Hello everyone, I hope you are all doing well.

Currently I have this table:

PatientID	Stage	Date	Score
1	Assessment	1/01/2025	15
1	Intervention	1/02/2025	11
1	Intervention	1/03/2025	13
1	Ending	1/04/2025	7
2	Intervention	1/05/2025	5

I want the user to be able to compare the score that a patient got in any given point in the intervention vs the score that the patient got either in the assessment or in the ending. 

I want a table that shows, for example, the score that the patient with ID=1 got on the Intervention stage at 1/02/2025 (11), the score that same patient got in the Assessment Stage (15), and the difference between both scores (4).

Thank you in advance.

[v-dineshya · Accepted solution]

Hi @PedroValmor ,

Thank you for reaching out to the Microsoft Community Forum.

To compare each Intervention with the corresponding Assessment or Ending for the same patient,.

Please follow below steps.

1. Create a new calculated table (Comparison Table) with below DAX.

2. To compare each Intervention with the corresponding Assessment or Ending for the same patient, use the following DAX to create a new Calculated table:

ComparisonTable =
VAR InterventionRows =
FILTER(
'Table',
'Table'[Stage] = "Intervention"
)
RETURN
ADDCOLUMNS(
InterventionRows,
"AssessmentScore",
CALCULATE(
MAX('Table'[Score]),
FILTER(
'Table',
'Table'[PatientID] = EARLIER('Table'[PatientID]) &&
'Table'[Stage] = "Assessment"
)
),
"EndingScore",
CALCULATE(
MAX('Table'[Score]),
FILTER(
'Table',
'Table'[PatientID] = EARLIER('Table'[PatientID]) &&
'Table'[Stage] = "Ending"
)
),
"DifferenceFromAssessment",
ABS([Score] -
CALCULATE(
MAX('Table'[Score]),
FILTER(
'Table',
'Table'[PatientID] = EARLIER('Table'[PatientID]) &&
'Table'[Stage] = "Assessment"
)
)
),
"DifferenceFromEnding",
ABS([Score] -
CALCULATE(
MAX('Table'[Score]),
FILTER(
'Table',
'Table'[PatientID] = EARLIER('Table'[PatientID]) &&
'Table'[Stage] = "Ending"
)
)
)
)

3. Use the table visual, and place  all columns from Comparison Table.

Please check the attached snap.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @PedroValmor ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[maruthisp]

Hi @PedroValmor ,

I hope, your table has columns like PatiendID, Stage, Date,Score.
Below DAX to pick up each patient's score in the assessment stage:
AssessmentScore =
CALCULATE(
MAX( PatientScores[Score] ), // or use SELECTEDVALUE if you’re guaranteed exactly one
FILTER(
ALL( PatientScores ), // ignore any other filters on Stage/Date
PatientScores[PatientID] = SELECTEDVALUE( PatientScores[PatientID] )
&& PatientScores[Stage] = "Assessment"
)
)

Then calculate "Intervention vs Assessment Delta" DAX expression:
ScoreDelta_IntervVsAssess =
SUM( PatientScores[Score] ) // in a matrix row where Stage="Intervention", this is the intervention score
- [AssessmentScore] // subtract the baseline measure from above

Try to use Matrix visual with rows and columns and values.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[Shravan133]

Create a calculated column for Assessment Score

AssessmentScore =

CALCULATE(

    MAX(PatientScores[Score]),

    FILTER(

        PatientScores,

        PatientScores[PatientID] = EARLIER(PatientScores[PatientID]) &&

        PatientScores[Stage] = "Assessment"

    )

)

Create a calculated column for Ending Score

EndingScore =

CALCULATE(

    MAX(PatientScores[Score]),

    FILTER(

        PatientScores,

        PatientScores[PatientID] = EARLIER(PatientScores[PatientID]) &&

        PatientScores[Stage] = "Ending"

    )

)

Create a calculated column for Difference from Assessment

Only calculate this for Intervention stage rows:

DiffFromAssessment =

IF(

    PatientScores[Stage] = "Intervention",

    PatientScores[Score] - PatientScores[AssessmentScore],

    BLANK()

)

(Optional) Difference from Ending:

DiffFromEnding =

IF(

    PatientScores[Stage] = "Intervention",

    PatientScores[Score] - PatientScores[EndingScore],

    BLANK()

)

Then add a filter to only show Stage = "Intervention" if you want to only compare those rows.