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 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?

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

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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"
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

       

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

       

      Regards,

      Amit Darak

  • Tahreem24's avatar
    Tahreem24
    Super User
    4 years ago

    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"
    )
     
     
  • vojtechsima's avatar
    vojtechsima
    Super 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