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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX to count occurrence number of a value in a column

Hi,

 

I am trying to count the occurence number of a value using DAX. The output needed is as follows:

 

Input              Output

A                          1

A                          2

B                          1
C                          1 
D                          1

B                           2

 

I found a way to do this is Power Query using the following formula for a new column:

= Table.AddColumn(#"Added Index", "Output", each Number.Abs(List.Count(List.RemoveItems(List.Range(#"Added Index"[Tracking Number], 0, [Index]), {[Tracking Number]}))-List.Count(List.Range(#"Added Index"[Tracking Number], 0, [Index]))))

 

The problem with this is that it takes forever to load values in the new column and I only once got an output after hours of waiting.

 

Any help on this would be appreciated. Thanks in advance.

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

I believe the sort order within the group should be preserved but let me know if it's not.

See the below, revised to accommodate other columns.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUy6wERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Input"}, {{"tbl", each _, type table [Input=text]}}),
    #"Add Index to tbl" = Table.TransformColumns( #"Grouped Rows", {{ "tbl", each Table.AddIndexColumn( _, "Index", 1, 1 ) , type table [Input=text, Index=number] }} )
in
    #"Add Index to tbl"
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

View solution in original post

6 REPLIES 6
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Give this a go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUy6wERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Input"}, {{"Output", each Table.AddIndexColumn( _, "Output", 1, 1 )[Output], type list }}),
    #"Expanded Output" = Table.ExpandListColumn(#"Grouped Rows", "Output"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Output",{{"Output", Int64.Type}})
in
    #"Changed Type1"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

Anonymous
Not applicable

Thank you for your quick response @Mariusz . Your solution seems to work but I have other columns in this table and they disappear when I use your code. I guess I should have framed my question a little better.

 

There are other columns as well in this table that are needed in the final output. Also,  grouping the column changes the way the data was sorted. When grouping, does the original order in which a particular value appear stay intact or are the values sorted again?

I can check this if I can keep index column in the output but I need a solution that lets me keep all the original columns as well.

Hi @Anonymous 

 

I believe the sort order within the group should be preserved but let me know if it's not.

See the below, revised to accommodate other columns.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YGRTmDSGUy6wERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Input"}, {{"tbl", each _, type table [Input=text]}}),
    #"Add Index to tbl" = Table.TransformColumns( #"Grouped Rows", {{ "tbl", each Table.AddIndexColumn( _, "Index", 1, 1 ) , type table [Input=text, Index=number] }} )
in
    #"Add Index to tbl"
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

Anonymous
Not applicable

@Mariusz I figured out where I was going wrong and your solution works. Thanks for your help!

Hi @Anonymous 

 

Happy you worked it out

 

Many Thanks

Mariusz

Anonymous
Not applicable

@Mariusz I tried using your suggestion but am still unable to get all the columns in final output. Instead it is just returning the Input and output columns and maybe that is because I am doing something wrong.

 

The last three steps before I need to perform this operation are as follows:

 

#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Others", type number}, {"International Freight", type number}, {"Units", type number}, {"Entry", type text}, {"Weight (Kg)", type number}, {"Pending Cases", Int64.Type}, {"No. of cases", Int64.Type},{"Arrival Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type3",{{"Date", Order.Ascending}, {"FileNo", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
in
#"Added Index"

 

I would like to see all of the columns mentioned in #"Changed Type3". The column by which we are supposed to group by here would be "Tracking Number".

 

Thank you for your continued support on this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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