Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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 Level Detail-based on mentioned dots.
Example: For CBL.0006412.02.01.01 it ends with .01 which is the last one and my condition should identify and show me whether it is Aggregator/Lowest Level Detail. And, if you see CBL.0006413.02.01 & CBL.0006413.02.02, it shows Lowest Level Detail.
CBL.0006412 | Aggregator |
CBL.0006412.01 | Aggregator |
CBL.0006412.02 | Aggregator |
CBL.0006412.02.01 | Aggregator |
CBL.0006412.02.01.01 | Lowest level detail |
CBL.0006413 | Aggregator |
CBL.0006413.01 | Aggregator |
CBL.0006413.02 | Aggregator |
CBL.0006413.02.01 | Lowest level detail |
CBL.0006413.02.02 | Lowest level detail |
Solved! Go to Solution.
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!!
Really Thanks Vahid for your quick help, you made my day. Thanks a lot again and you are Power BI & DAX expert.
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!!
User | Count |
---|---|
13 | |
10 | |
8 | |
7 | |
5 |
User | Count |
---|---|
24 | |
16 | |
15 | |
10 | |
7 |