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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Jessica_17
Helper III
Helper III

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

 

1 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

vxinruzhumsft_0-1701408534639.png

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.

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

I have solved a similar problem in the attached file.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur 

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

v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1701395709242.png

 

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.

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?

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

vxinruzhumsft_0-1701408534639.png

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.

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

Jessica_17_0-1701493050726.png

 

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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