Forum Discussion

Melodyv's avatar
Melodyv
Icon for Helper I rankHelper I
4 years ago
Solved

Conditional column with complex multiple conditions

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 comp...
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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:

     

     

v-henryk-mstf's avatar
v-henryk-mstf
Icon for Community Support rankCommunity Support
4 years ago

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