Counting Types of Records

[DashboardNewbie]

This may seem like the most basic of problems, but I have an xls file with 100,000+ customer rows.  There are 300+ columns against each of the rows that classify the customer in two ways 

Descriptive Columns:  such as Age  / Gender and as you can tell, these have pre-set values filled in such as 

Under Age, 18-24 or 25 - 34 and so on..

Under Gender, Values are M or F

Binary Columnes:  These are questions answered in a yes/no fashion, such as 

Interested in Movies: Y/N

Has Cable at Home: Y/N

I am trying to create an audience persona with this data, that helps me create a report that takes each column name and shows me the count of each of the distinct values in the column.  So for example, for "Gender" Count of Male / Female, and for "Interested in Movies" Count of Y

How do I transform my table - I know this is a lengthy problem but I am an absolute beginner and am trying to learn the most basic transformation to generate some reports

Thank you all in advance for your help for pointing me in the right direction 

[BhaveshPatel]

Hi there

Connect to the your excel file and import data into powerBI making sure that all the data types are correctly interpreted by the PowerBI Engine. 

You can drag and drop the particular columns into the values sections againest columns and rows. 

It can also give the value of distinct count if you select the drop down of the field/s in the values section. 

Use table Visual for this purpose. Hope this will help you understand.

or use 

COUNT or DISTINCTCOUNT  DAX Formula by selecting one of the columns from your table and right clicking and choosing new measure option.

[DashboardNewbie]

Thanks Bhavesh for the help, and I could see the totals against each category in the drop down as you suggested.. However I'm not sure how to get the data in the table visual against each category... 

Here's an example screenshot of my data

And, this is where I would like the count of each of the age categories to appear

[Eric_Zhang · Accepted solution]

@DashboardNewbie

If I understand your requirement correctly, for a table as below,

You will need two calculated tables, one as

Table = 
SUMMARIZE (
    Table13,
    "CNT Female", COUNTAX (
        FILTER ( Table13, Table13[Under Gender] = "F" ),
        Table13[Under Gender]
    ),
    "CNT Interested in Movie", COUNTAX (
        FILTER ( Table13, Table13[Interested in Movies] = "Y" ),
        Table13[Interested in Movies]
    ),
    "CNT Has Cable at home", COUNTAX (
        FILTER ( Table13, Table13[Has Cable at Home] = "Y" ),
        Table13[Has Cable at Home]
    ),
    "CNT 18-24", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "18-24" ), Table13[Under Age] ),
    "CNT 25-34", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "25-34" ), Table13[Under Age] ),
    "CNT 35-44", COUNTAX ( FILTER ( Table13, Table13[Under Age] = "35-44" ), Table13[Under Age] )
)

The other as

Table2 = 
UNION (
    SELECTCOLUMNS ( 'Table', "category", "cnt Female", "cnt", 'Table'[CNT Female] ),
    SELECTCOLUMNS (
        'Table',
        "category", "cnt Has Cable at home",
        "cnt", 'Table'[CNT Has Cable at home]
    ),
    SELECTCOLUMNS (
        'Table',
        "category", "cnt Intetrested in Movie",
        "cnt", 'Table'[CNT Interested in Movie]
    ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 18-24", "cnt", 'Table'[CNT 18-24] ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 25-34", "cnt", 'Table'[CNT 25-34] ),
    SELECTCOLUMNS ( 'Table', "category", "cnt 35-44", "cnt", 'Table'[CNT 35-44] )
)

[EdwardXAult]

how does one modify the FILTER argument if the categories are multivariable i.e. M2M datamodel in lieu of the flat table in this example?

[jscottNRG]

I'm new to Power BI and DAX and this thread has been useful for a similar task.  However, I'm having trouble figuring out how to get the calculated tables with the counts of each type of record to update when a slicer selection is made.  As far as I know, I'm unable to create a relationship between the calculated tables and the original data tables because by design the calculated tables are summary tables.

For example (going off of the OP question), say I need to slice the summary results by U.S. state that the customer's order came from.  I have order state in another table, with a relationship established with the customer data table offered by DashboardNewbie via an Order ID.   How might I add a slicer with order state that will update a table visualization that contains the calculated tables built with Eric_Zhang's calculated tables?

Thanks in advance for any guidance!   

[DashboardNewbie]

Thank you @Eric_Zhang This should do the trick, I'll let you know how it goes - My data set has 300 similar categories, so I hope it's a fast enough solution to deploy