Forum Discussion

Jessica_17's avatar
Jessica_17
Icon for Helper V rankHelper V
2 years ago
Solved

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

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_stepbi_jh_eq_nomed_qty
202309900  2111
202309900  2593
202309900  2626
202309900  2639
202309900  26511
202309900  26614
202309900  26817
202309900  26918
202309900  27019
202309900  27121
202309900  27224
202309901  2731
202309901  2754
202309901  2767
202309901  27710
202309901  27811
202309901  27913
202309901  28015

 

Output:- 

Hour_stepbi_jh_eq_nomed_qty
202309900  2111
202309900  2592
202309900  2623
202309900  2634
202309900  2655
202309900  2666
202309900  2687
202309900  2698
202309900  2709
202309900  27110
202309900  27211
202309901  2731
202309901  2752
202309901  2763
202309901  2774
202309901  2785
202309901  2796
202309901  2807

 

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Jessica_17
      Icon for Helper V rankHelper V
      2 years ago

      Hello Anonymous 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.