topic Re: How to count the occurence of a value from a column in a coma separated field in another column? in DAX Commands and Tips

How to count the occurence of a value from a column in a coma separated field in another column?

Betty888 — Mon, 17 Jun 2024 23:54:03 GMT

Dear all,

I'm trying to count the number of occurence of a value from column "Date" in a list of dates with a semicolon separator in another column "Column B" as below :

Date	Column B
date1	date1; date2;date3;date4;date5;date6; ,,,,,
date2	date1; date2;date3;date4;date5;date6; ,,,,,
date3	date1; date2;date3;date4;date5;date6; ,,,,,
date4	date1; date2;date3;date4;date5;date6; ,,,,,

Can you help me with any ideas, please 🙏

Thank you in advanace for your help !

Regards,

Re: How to count the occurence of a value from a column in a coma separated field in another column?

Moetazzahran — Tue, 18 Jun 2024 01:01:30 GMT

Hello Betty888,

I created this calculated column which uses two main variables
The first variable

OccurrenceLength is the difference between the length of the text of [ColumnB] and the length of the same text after it replaces the variable in [Date] with a blank().

This difference in length will then be divided by the length of the variable we replaced within the string to get the number of occurance of the variable you are searching for within the ColumnB

Please let me know if this works, and accept it as a solution if it does. You are kudo is also much appreciated.

Re: How to count the occurence of a value from a column in a coma separated field in another column?

Anonymous — Tue, 18 Jun 2024 01:30:36 GMT

Hi, Betty888

You can create a custom column and use the following M expression in PowerQuery. This formula splits the text in "Column B" by semicolon and counts the number of elements after splitting. It then adds one to the count result. If there is no data after the semicolon, we need to subtract one from it.

= List.Count(Text.Split([Column B], ";"))-1

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

Re: How to count the occurence of a value from a column in a coma separated field in another column?

Betty888 — Fri, 28 Jun 2024 10:57:35 GMT

Thanks a lot !

In fact, your suggested solution counts the number of items, but what I need is to count the number of occurence of value from column "Date" in "Column B".

Regards,

Re: How to count the occurence of a value from a column in a coma separated field in another column?

Betty888 — Fri, 28 Jun 2024 11:00:27 GMT

Many thanks !
it works for my problem, many many thanks !