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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Niiru1
Helper V
Helper V

Cumulative RANKX index COUNT OCCURANCE

I have a table

 

IDColumn
1231
1231
7252
7252
10033
10033
10033

 

Column = RANKX('Full Table',[ID],,ASC,Dense),
 
is there a way to count the occurances:
IDColumn
1231
1232
7251
7252
10031
10032
10033
1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

Hi, @Niiru1 , it's easier to achieve that in Power Query, as shown below,

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVQdDmRqYotKGBgTE2RiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"ID", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each {1..Table.RowCount(_)}}}, GroupKind.Local),
    #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "Count")
in
    #"Expanded Count"

 

If you insist on DAX, it can be also achieved after adding an index column,

Rank Column =
RANKX (
    FILTER ( 'RankTable', 'RankTable'[ID] = EARLIER ( 'RankTable'[ID] ) ),
    'RankTable'[Index],
    ,
    ASC
)

Screenshot 2020-10-25 135032.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@Niiru1  Hey Mate,

You can use below one . You have to create a column to get number of occurance 

Occurence = 
COUNTX (
    FILTER ( 'Full Table', EARLIER ( 'Full Table'[ID] ) = 'Full Table'[ID]  ),
    'Full Table'[ID] 
)

 Capture 2.JPG

 

Thank you mates @Niiru1 , @CNENFRNL , @Fowmy  , @AlB 

CNENFRNL
Community Champion
Community Champion

Hi, @Niiru1 , it's easier to achieve that in Power Query, as shown below,

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVQdDmRqYotKGBgTE2RiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"ID", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each {1..Table.RowCount(_)}}}, GroupKind.Local),
    #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "Count")
in
    #"Expanded Count"

 

If you insist on DAX, it can be also achieved after adding an index column,

Rank Column =
RANKX (
    FILTER ( 'RankTable', 'RankTable'[ID] = EARLIER ( 'RankTable'[ID] ) ),
    'RankTable'[Index],
    ,
    ASC
)

Screenshot 2020-10-25 135032.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

colacan
Resolver II
Resolver II

@Niiru1  Hi, Niiru, By introducing ID2 = ID+random decimal number, you can give rank to within each ID group. for example,

 

ID_Decimal = RankTable[ID] + RAND()
 
then, tyr below.
 
NewRank_Column =
VAR currentID = RankTable[ID]
VAR currentID_Decimal = RankTable[ID_Decimal]
VAR ID_FilteredTable =
FILTER (
RankTable,
AND ( RankTable[ID] = currentID, RankTable[ID_Decimal] < currentID_Decimal )
)
VAR RankRevised =
COUNTROWS ( ID_FilteredTable ) + 1
RETURN
RankRevised
Fowmy
Super User
Super User

@Niiru1 

What is your actual scenario where you need to add a sequence column? In DAX, you need a value column to rank by. 

If you just need a sequence number, Use power Query as @AlB  suggested.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

AlB
Super User
Super User

Hi @Niiru1 

This is best done in Power Query. Paste the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVorVQdDmRqYotKGBgTE2RiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Column", each List.Numbers(1,[Count])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Column"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"})
in
    #"Removed Columns"

 If you want it in DAX you'll need to add an index column in Power Query first in any case.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors