Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
filexsundeep09
Frequent Visitor

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.01CBL.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

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

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:

VahidDM_0-1658235113386.png

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

View solution in original post

2 REPLIES 2
filexsundeep09
Frequent Visitor

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

VahidDM
Super User
Super User

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:

VahidDM_0-1658235113386.png

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.