Forum Discussion
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...
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] ) )
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] )
)
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?