Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Month | Product | region | Count | reqd |
202001 | Product A | AMS | 0 | |
202002 | Product A | AMS | 15 | M1 |
202003 | Product A | AMS | 20 | M2 |
202004 | Product A | AMS | 25 | M3 |
202005 | Product A | AMS | 30 | M4 |
202006 | Product A | APJ | 5 | M1 |
202007 | Product A | APJ | 10 | M2 |
202008 | Product A | APJ | 15 | M3 |
202009 | Product A | EMEA | 5 | M1 |
202010 | Product A | EMEA | 10 | M2 |
202011 | Product A | EMEA | 15 | M3 |
202012 | Product A | EMEA | 20 | M4 |
202101 | Product B | AMS | 12 | M1 |
202102 | Product B | AMS | 23 | M2 |
202103 | Product B | AMS | 35 | M3 |
202104 | Product B | AMS | 0 | M4 |
202105 | Product B | AMS | 40 | M5 |
202106 | Product B | AMS | 27 | M6 |
202107 | Product B | EMEA | 5 | M1 |
202108 | Product B | EMEA | 10 | M2 |
202109 | Product B | EMEA | 15 | M3 |
202110 | Product B | EMEA | 20 | M4 |
202111 | Product B | EMEA | 25 | M5 |
202112 | Product B | EMEA | 30 | M6 |
Solved! Go to Solution.
Hi, @Mahadevaraobc
You can try the following methods.
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] )
)
)
)
reqd =
IF ( [Column] <> BLANK (), CONCATENATE ( "M", [Column] ), BLANK () )
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.
Hi, @Mahadevaraobc
You can try the following methods.
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] )
)
)
)
reqd =
IF ( [Column] <> BLANK (), CONCATENATE ( "M", [Column] ), BLANK () )
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.
This looks like a good problem that can be solved by RANKX with DENSE
If you are doing a SQL query, you could also do it with DENSE_RANK() OVER() as well
Hi Experts,
Please help...
User | Count |
---|---|
136 | |
73 | |
73 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
63 | |
63 | |
51 |