Forum Discussion
Solved
Conditional column based to identify the possible lowest level
Experts, please someone help me with the below scenario to create the conditional column based on the lowest possible value. Below, the second column should show me whether it is Aggregator/Lowest Le...
Use this code to add a new column to your table:
Column = VAR _NDot = LEN ( 'Table'[Item] ) - ( LEN ( SUBSTITUTE ( 'Table'[Item], ".", "" ) ) + 1 ) VAR _Dot_Group = GROUPBY ( SUMMARIZE ( 'Table', 'Table'[Item], "AA", LEFT ( 'Table'[Item], 11 ), "BB", LEN ( 'Table'[Item] ) - ( LEN ( SUBSTITUTE ( 'Table'[Item], ".", "" ) ) + 1 ) ), [AA], "CCD", MAXX ( CURRENTGROUP (), [BB] ) ) VAR _Max_Group = MAXX ( FILTER ( _Dot_Group, [AA] = LEFT ( 'Table'[Item], 11 ) ), [CCD] ) RETURN IF ( _NDot = _Max_Group, "Lowest level detail", "Aggregator" )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
VahidDM
Super User
Super User
Use this code to add a new column to your table:
Column =
VAR _NDot =
LEN ( 'Table'[Item] )
- ( LEN ( SUBSTITUTE ( 'Table'[Item], ".", "" ) ) + 1 )
VAR _Dot_Group =
GROUPBY (
SUMMARIZE (
'Table',
'Table'[Item],
"AA", LEFT ( 'Table'[Item], 11 ),
"BB",
LEN ( 'Table'[Item] )
- ( LEN ( SUBSTITUTE ( 'Table'[Item], ".", "" ) ) + 1 )
),
[AA],
"CCD", MAXX ( CURRENTGROUP (), [BB] )
)
VAR _Max_Group =
MAXX ( FILTER ( _Dot_Group, [AA] = LEFT ( 'Table'[Item], 11 ) ), [CCD] )
RETURN
IF ( _NDot = _Max_Group, "Lowest level detail", "Aggregator" )
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!