topic Count values by category in specific columns characterized by similar names in DAX Commands and Tips

Count values by category in specific columns characterized by similar names

flyingfox — Tue, 20 Feb 2024 16:55:06 GMT

Hello, I am trying to figure out how to implement with DAX

Consider the following matrix, where some column names start with e.g. “Field”

Date	Category	Type	Field1	Field2	Field3	Field4	Field5	Field6	Field7
dd/mm/yyyy	A	Peak	Value1
dd/mm/yyyy	B	OffPeak	Value2	Value3	Value33
dd/mm/yyyy	C	Revenue	Value44	Value15	Value16	Value18	Value20	Value22	Value25
dd/mm/yyyy	C	Other	Value3	Value25	Value85	Value45	Value40	Value41
dd/mm/yyyy	A	Other	Value55	Value56	Value68	Value78	Value7

I would like to count for each Category A, B, C how many values are in the column named “Fieldxx” with xx that could vary from 1 to n.

In the example the result is

Category	Count
A	6
B	3
C	13

Thank you for your support. Bye

Re: Count values by category in specific columns characterized by similar names

Anonymous — Wed, 21 Feb 2024 02:49:13 GMT

Hi flyingfox ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1. Open power query and select all the "Fieldxx" columns, right-click, and choose Unpivot Columns.

2.Close and apply and create a meaure

CountValuesByCategory = SUMX( VALUES('Table'[Category]), CALCULATE( COUNTROWS('Table'), 'Table'[Value] <> BLANK() ) )

3.Final output

Re: Count values by category in specific columns characterized by similar names

flyingfox — Wed, 21 Feb 2024 11:01:30 GMT

Thank you for your solution. Unpivoting the columns could work, but I should repeat manually these steps whenever I had to elaborate a new table with a different number of columns named “Fieldxx”. Do you have any other suggestions in order to avoid to execute unpivot specific columns?

Re: Count values by category in specific columns characterized by similar names

Anonymous — Thu, 22 Feb 2024 09:46:54 GMT

Hi flyingfox ,
Thank you for your reply. You can still use this method, just change the selected columns in the step to the first three fixed columns and use the unpivot other column function, so that this method applies no matter how many FiledXXs come after that.

Best regards,

Albert He

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