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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Mahadevaraobc
Helper II
Helper II

new column based on a condition

Hi Experts,

Require you help in achieving the following marked as reqd.

If Product starts from any month greater than 0 then that month should be considered as M1 and following months should be M2,M3 so on, it should also be based on Region.

in between of the month even if there is 0 that should also be considered as continuation of M1, m2,

Hope this clarifies let me know if additional info reqd.

 

MonthProductregionCountreqd
202001Product AAMS0 
202002Product AAMS15M1
202003Product AAMS20M2
202004Product AAMS25M3
202005Product AAMS30M4
202006Product AAPJ5M1
202007Product AAPJ10M2
202008Product AAPJ15M3
202009Product AEMEA5M1
202010Product AEMEA10M2
202011Product AEMEA15M3
202012Product AEMEA20M4
202101Product BAMS12M1
202102Product BAMS23M2
202103Product BAMS35M3
202104Product BAMS0M4
202105Product BAMS40M5
202106Product BAMS27M6
202107Product BEMEA5M1
202108Product BEMEA10M2
202109Product BEMEA15M3
202110Product BEMEA20M4
202111Product BEMEA25M5
202112Product BEMEA30M6

 

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @Mahadevaraobc 

 

You can try the following methods.

  1. Calculated column

 

Column = 
IF (
    [Month]
        = CALCULATE (
            MIN ( 'Table'[Month] ),
            FILTER ( 'Table', [Count] = 0 && [Month] = MIN ( 'Table'[Month] ) )
        ),
    BLANK (),
    COUNTROWS (
        FILTER (
            'Table',
            [Product] = EARLIER ( 'Table'[Product] )
                && [region] = EARLIER ( 'Table'[region] )
                && [Month] <= EARLIER ( 'Table'[Month] )
                && [Month] <> MIN ( 'Table'[Month] )
        )
    )
)

 

vzhangti_0-1636963823103.jpeg

 

  1. Add "M" with function

 

reqd = 
IF ( [Column] <> BLANK (), CONCATENATE ( "M", [Column] ), BLANK () )

 

 

vzhangti_1-1636963870209.png

 

Best Regards,

Community Support Team _Charlotte

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

View solution in original post

3 REPLIES 3
v-zhangti
Community Support
Community Support

Hi, @Mahadevaraobc 

 

You can try the following methods.

  1. Calculated column

 

Column = 
IF (
    [Month]
        = CALCULATE (
            MIN ( 'Table'[Month] ),
            FILTER ( 'Table', [Count] = 0 && [Month] = MIN ( 'Table'[Month] ) )
        ),
    BLANK (),
    COUNTROWS (
        FILTER (
            'Table',
            [Product] = EARLIER ( 'Table'[Product] )
                && [region] = EARLIER ( 'Table'[region] )
                && [Month] <= EARLIER ( 'Table'[Month] )
                && [Month] <> MIN ( 'Table'[Month] )
        )
    )
)

 

vzhangti_0-1636963823103.jpeg

 

  1. Add "M" with function

 

reqd = 
IF ( [Column] <> BLANK (), CONCATENATE ( "M", [Column] ), BLANK () )

 

 

vzhangti_1-1636963870209.png

 

Best Regards,

Community Support Team _Charlotte

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

Anonymous
Not applicable

This looks like a good problem that can be solved by RANKX with DENSE

 

https://dax.guide/rankx/

 

If you are doing a SQL query, you could also do it with DENSE_RANK() OVER() as well

Mahadevaraobc
Helper II
Helper II

Hi Experts,

Please help...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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