Conditional column based to identify the possible lowest level

[filexsundeep09]

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

[VahidDM · Accepted solution]

Hi @filexsundeep09 

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

LinkedIn | Twitter | Blog | YouTube 

[filexsundeep09]

Really Thanks Vahid for your quick help, you made my day. Thanks a lot again and you are Power BI & DAX expert.