Conditional column with complex multiple conditions

[Melodyv]

Hi all - I am trying to create a custom column using multiple conditions across multiple columns.  I haven't quite seen a question or solution from other posts that appear to be as complex.  I'm comparing pre and post scores on a family assessment.  The assessment has multiple domains (ex: Housing/Shelter) and the score ranges reflect a crisis to thriving matrix.  I'm unable to share a document since the source file contains identifiable info, but hopefully a screenshot will help.

I am trying to create a custom column that will compare the pre/post score ranges for each domain and generate "Gain," "Loss," or "Maintain."  For example, line 16 of the screenshot below shows the family scored "Stable" on Housing/Shelter for their Pre assessment.  Line 32 shows the same family scored "Vulnerable" on Housing/Shelter for their Post assessment.  I want to create a custom column that will report "Loss."

Here's how the Gain/Loss/Maintain would be determined:

"SSM Pre" for "Housing/Shelter" = "In Crisis" and "SSM Post" for "Housing/Shelter" = "Vulnerable" or "Safe" or "Stable" or Thriving" = "Gain"

"SSM Pre" for "Housing/Shelter" = "Vulnerable" and "SSM Post" for "Housing/Shelter" = "Safe" or "Stable" or "Thriving" = "Gain"

"SSM Pre" for "Housing/Shelter" = "Safe" and "SSM Post" for "Housing/Shelter" = "Stable" or "Thriving" = "Gain"

And so on.... plus all the other combinations leading to a "Loss" or "Maintain" in the Custom Column.  Then I would use the same process to create additional custom columns for each of the other domains - Childcare, Food, Clothing, etc.

My guess is there is a much simpler way to do this had I not unpivoted the columns in a previous step.  However, I need it unpivoted for a different visual using the same spreadsheet to work.  Any help/suggestions appreciated. I even considered just uploading the same spreadsheet with a slightly different name, re-applying the steps up until the unpivot columns, and trying to work with it from there. I am fairly new to PowerBI.  Thank you!!!!!

[v-henryk-mstf]

Hi @Melodyv ,

Whether the advice given by @DataInsights  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[DataInsights · Accepted solution]

@Melodyv,

Try this solution. It uses a ranking table DimScore that has a relationship with the fact table.

DimScore:

Data model:

Calculated column in FactTable:

Gain/Loss/Maintain = 
VAR vFamilyID = FactTable[Family ID]
VAR vDomain = FactTable[SSM Domain]
VAR vPreScore =
    MAXX (
        FILTER (
            FactTable,
            FactTable[Family ID] = vFamilyID
                && FactTable[Assessment] = "SSM Pre"
                && FactTable[SSM Domain] = vDomain
        ),
        RELATED ( DimScore[Rank] )
    )
VAR vPostScore =
    MAXX (
        FILTER (
            FactTable,
            FactTable[Family ID] = vFamilyID
                && FactTable[Assessment] = "SSM Post"
                && FactTable[SSM Domain] = vDomain
        ),
        RELATED ( DimScore[Rank] )
    )
VAR vResult =
    SWITCH (
        TRUE,
        vPreScore < vPostScore, "Gain",
        vPreScore > vPostScore, "Loss",
        "Maintain"
    )
RETURN
    vResult

Result: