Serial number on grouped data for based on one column itself which are text columns

[Jessica_17]

Hello All,
Can anyone please help me, I have a table which has datetimestamp, each datetimestamp has name which can be integer or text value, I want to create a serial number in table visualization for the same, but when I write any measure , it is providing value as shown in med_qty column. 

Here is the formula for med_qty = 

CALCULATE(COUNTROWs(DISTINCT('public bi_job_hist cntr_attr')), FILTER(ALLSELECTED('public bi_job_hist cntr_attr'), 'public bi_job_hist cntr_attr'[bi_jh_eq_no] <=  MAX('public bi_job_hist cntr_attr'[bi_jh_eq_no]) ))

the serial number should be recreated basis of any filter selected and for every datetime stamp.

data:-

Hour_step	bi_jh_eq_no	med_qty
202309900	211	1
202309900	259	3
202309900	262	6
202309900	263	9
202309900	265	11
202309900	266	14
202309900	268	17
202309900	269	18
202309900	270	19
202309900	271	21
202309900	272	24
202309901	273	1
202309901	275	4
202309901	276	7
202309901	277	10
202309901	278	11
202309901	279	13
202309901	280	15

Output:- 

Hour_step	bi_jh_eq_no	med_qty
202309900	211	1
202309900	259	2
202309900	262	3
202309900	263	4
202309900	265	5
202309900	266	6
202309900	268	7
202309900	269	8
202309900	270	9
202309900	271	10
202309900	272	11
202309901	273	1
202309901	275	2
202309901	276	3
202309901	277	4
202309901	278	5
202309901	279	6
202309901	280	7

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.

Hope this helps.

[Jessica_17]

Hello @Ashish_Mathur 

Thanks for the solution, but it did not worked the same way in my dashboard.

[v-xinruzhu-msft]

Hi @Jessica_17 

You can create a measure

Measure =
RANKX (
    FILTER (
        ALLSELECTED ( 'public bi_job_hist cntr_attr' ),
        [Hour_step] IN VALUES ( 'public bi_job_hist cntr_attr'[Hour_step] )
    ),
    CALCULATE ( MAX ( 'public bi_job_hist cntr_attr'[bi_jh_eq_no] ) ),
    ,
    ASC,
    DENSE
)

Output

Best Regards!

Yolo Zhu

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

[Jessica_17]

Hello @v-xinruzhu-msft 

Thanks for the solution it worked,
one more doubt, how can I get the max value for each hour_step in another measure?

[v-xinruzhu-msft · Accepted solution]

Hi @Jessica_17 

If you want to get the max value of the measure, you can refer to the following measure

Max_measure_no =
CALCULATE (
    MAXX ( 'public bi_job_hist cntr_attr', [Measure] ),
    ALLSELECTED ( 'public bi_job_hist cntr_attr' ),
    'public bi_job_hist cntr_attr'[Hour_step]
        IN VALUES ( 'public bi_job_hist cntr_attr'[Hour_step] )
)

if you want to get the max value of the eq_no,  you can refer to the following measure

Measure_eq_no =
MAXX (
    FILTER (
        ALLSELECTED ( 'public bi_job_hist cntr_attr' ),
        [Hour_step] IN VALUES ( 'public bi_job_hist cntr_attr'[Hour_step] )
    ),
    [bi_jh_eq_no]
)

Output

Best Regards!

Yolo Zhu

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

[Jessica_17]

Hello @v-xinruzhu-msft 

I want rank value , but it is not working for me it is showing 1 as value, whereas I want 19 as max value, and hour_step and eq_no columns are text in my case

[v-xinruzhu-msft]

Hi @Jessica_17 

The measure can work in my data, can you provide the code you have input?and the requirement is a new requirement, you can create a new post and if my solution above help to solve the original question, please consider to mark it as a solution.

Best Regards!

Yolo Zhu

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

Best Regards!

Yolo Zhu