Forum Discussion

filexsundeep09's avatar
filexsundeep09
Frequent Visitor
4 years ago
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 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's avatar
    VahidDM
    4 years ago

    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 

2 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
    filexsundeep09
    Frequent Visitor
    4 years ago

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