Create a new column based on multiple columns and grouping

[Anonymous]

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 from above data, if 1 employee code has
- Self then E,
- Self, Spouse, Children, Parent then ESCP.
- Self, Spouse then ES

- Self, Parent then SP and so on.

Also taking year into consideration is also important as previous year combination may be different as is the case for empl code 123.

How can this be achieved?

[vojtechsima]

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

[Tahreem24]

@Anonymous Try this measure:

Measure 2 =

VAR a_ = CALCULATE(COUNTROWS(FamilyTable),ALLEXCEPT(FamilyTable,FamilyTable[Emp Code],FamilyTable[Year]))

VAR EP_ = CALCULATE(COUNTROWS(FamilyTable),FamilyTable[Relation] in {"Self","Parent"},ALLEXCEPT(FamilyTable,FamilyTable[Year],FamilyTable[Emp Code]))

VAR SP_ = CALCULATE(COUNTROWS(FamilyTable),FamilyTable[Relation] in {"Self","Spouse"},ALLEXCEPT(FamilyTable,FamilyTable[Year],FamilyTable[Emp Code]))

VAR ESC_ = CALCULATE(COUNTROWS(FamilyTable),FamilyTable[Relation] in {"Self","Spouse","Chidren"},ALLEXCEPT(FamilyTable,FamilyTable[Year],FamilyTable[Emp Code]))

RETURN SWITCH(TRUE(),

a_ = 4, "ESCP",

a_ = 1, "E",

a_ = 2 && SP_=2, "ES",

a_ = 2 && EP_=2, "EP",

a_=3 && ESC_=2,"ESC"

)

 

[Jihwan_Kim · Accepted solution]

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"
    )

[Anonymous]

Hi,

Thanks a lot. This is exactly what I was looking for.

Regards,

Amit Darak