Forum Discussion

DashboardNewbie's avatar
DashboardNewbie
Regular Visitor
10 years ago
Solved

Counting Types of Records

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    Descrip...
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    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] )
)
DashboardNewbie's avatar
DashboardNewbie
Regular Visitor
10 years ago

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's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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] )
)
  • DashboardNewbie's avatar
    DashboardNewbie
    Regular Visitor
    10 years ago

    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 

  • jscottNRG's avatar
    jscottNRG
    Icon for Helper II rankHelper II
    9 years ago

    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!   

  • EdwardXAult's avatar
    EdwardXAult
    Regular Visitor
    8 years ago

    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?