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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
flyingfox
Regular Visitor

Count values by category in specific columns characterized by similar names

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”

 

DateCategoryTypeField1Field2Field3Field4Field5Field6Field7
dd/mm/yyyyAPeakValue1      
dd/mm/yyyyBOffPeakValue2Value3Value33    
dd/mm/yyyyCRevenueValue44Value15Value16Value18Value20Value22Value25
dd/mm/yyyyCOtherValue3Value25Value85Value45Value40Value41 
dd/mm/yyyyAOtherValue55Value56Value68Value78Value7  

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

 

CategoryCount
A6
B3
C13

Thank you for your support. Bye

1 ACCEPTED SOLUTION

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.

vheqmsft_0-1708595177773.png

 

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

View solution in original post

3 REPLIES 3
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1708483542404.png

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

vheqmsft_1-1708483630660.png

2.Close and apply and create a meaure

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

3.Final output

vheqmsft_2-1708483714735.png

 

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?

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.

vheqmsft_0-1708595177773.png

 

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!