Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Combining Columns to Get Count of Values

I have a bunch of columns in one table that have many of the same values. I need to create a calculated table that combines those columns into one column vertically (not horizontally such as concatenating), more like a union. Then I need to pivot the new column in the calculated table determine total number of occurences of each of the unique values. 

I can't do this in the query editor because I need the numbers to change based upon slicers that will be used in the report.

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous

     

    You can create a Calc Table from Modelling Tab >>New Table like

     

    Calculated Table =
UNION (
    SELECTCOLUMNS ( Table1, "Class", Table1[First Class] ),
    SELECTCOLUMNS ( Table1, "Class", Table1[Second Class] )
	.........
	.........
)

    Then in a table visual drag the the Class and Count of Classes from above calculated tabkle

     

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous

     

    You can create a Calc Table from Modelling Tab >>New Table like

     

    Calculated Table =
UNION (
    SELECTCOLUMNS ( Table1, "Class", Table1[First Class] ),
    SELECTCOLUMNS ( Table1, "Class", Table1[Second Class] )
	.........
	.........
)

    Then in a table visual drag the the Class and Count of Classes from above calculated tabkle