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:

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
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: