Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a new column based on multiple columns and grouping

Hi,   I need to create a new column based on family members under 1 employee code.  Below is the data that I have and column that I need to create ie Family Definition     As can be seen f...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below Calculated Column DAX formula and the attached pbix file.

    It is for creating a calculated column.

     

    Family definition CC =
VAR _conditiontable =
    SUMMARIZE (
        FILTER (
            Data,
            Data[Year] = EARLIER ( Data[Year] )
                && Data[Employee code] = EARLIER ( Data[Employee code] )
        ),
        Data[Relation]
    )
RETURN
    SWITCH (
        TRUE (),
        { "Self" }
            IN _conditiontable
            && { "Spouse" }
            IN _conditiontable
            && { "Children" }
            IN _conditiontable
            && { "Parent" } IN _conditiontable, "ESCP",
        { "Self" }
            IN _conditiontable
            && { "Spouse" }
            IN _conditiontable
            && { "Children" } IN _conditiontable, "ESC",
        { "Self" }
            IN _conditiontable
            && { "Spouse" } IN _conditiontable, "ES",
        { "Self" }
            IN _conditiontable
            && { "Parent" } IN _conditiontable, "EP",
        { "Self" } IN _conditiontable, "E",
        "Check the logic"
    )
vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
4 years ago

Hi, Anonymous 
I am also adding a different version as a measure:

 

 

 

 

Familiy definiton___ = 
var CurrentCode = SELECTEDVALUE ( FamilyDefinitionTable[Employee code] ) 
var CurrentYear = SELECTEDVALUE (FamilyDefinitionTable[Year] )
var theTable = CALCULATETABLE(SUMMARIZE(FamilyDefinitionTable, "Year", CurrentYear , "Emp_Code",CurrentCode, "AllRelations", CALCULATE(CONCATENATEX(FamilyDefinitionTable, FamilyDefinitionTable[Relation], ","))), ALLEXCEPT(FamilyDefinitionTable, FamilyDefinitionTable[Year],FamilyDefinitionTable[Employee code]))
var ADDRelations = 
ADDCOLUMNS(theTable, "Family Definition", 

    var CheckSelf = IF(CONTAINSSTRING([AllRelations], "Self"), "E")
    var CheckSpouse = IF(CONTAINSSTRING([AllRelations], "Spouse"), "S")
    var CheckChildren = IF(CONTAINSSTRING([AllRelations], "Children"), "C")
    var CheckParent = IF(CONTAINSSTRING([AllRelations], "Parent"), "P")
    var Together = CheckSelf&CheckSpouse&CheckChildren&CheckParent
return Together
)


var FinxTheCode = MAXX(FILTER(ADDRelations, [Year] = CurrentYear && [Emp_Code] = CurrentCode), [Family Definition])

return FinxTheCode

 

 

 

 

The check for the letters in this one is probably a bit cleaner.

As calculated column here:

FamilyColumn = 
var CurrentCode =  FamilyDefinitionTable[Employee code] 
var CurrentYear = FamilyDefinitionTable[Year] 
var theTable = CALCULATETABLE(SUMMARIZE(FamilyDefinitionTable, "Year", CurrentYear , "Emp_Code",CurrentCode, "AllRelations", CALCULATE(CONCATENATEX(FamilyDefinitionTable, FamilyDefinitionTable[Relation], ","))), ALLEXCEPT(FamilyDefinitionTable, FamilyDefinitionTable[Year],FamilyDefinitionTable[Employee code]))
var ADDRelations = 
ADDCOLUMNS(theTable, "Family Definition", 

    var CheckSelf = IF(CONTAINSSTRING([AllRelations], "Self"), "E")
    var CheckSpouse = IF(CONTAINSSTRING([AllRelations], "Spouse"), "S")
    var CheckChildren = IF(CONTAINSSTRING([AllRelations], "Children"), "C")
    var CheckParent = IF(CONTAINSSTRING([AllRelations], "Parent"), "P")
    var Together = CheckSelf&CheckSpouse&CheckChildren&CheckParent
return Together
)


var FinxTheCode = MAXX(FILTER(ADDRelations, [Year] = CurrentYear && [Emp_Code] = CurrentCode), [Family Definition])

return FinxTheCode