Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
@Anonymous Try this measure:
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"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Thanks a lot. This is exactly what I was looking for.
Regards,
Amit Darak
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |