Best practices for manipulating data

[ChrisWilliams]

I'm new to Power BI and I'm trying to learn the best practices for what might be common tasks.

In this example, think of a single table that I import from an OData feed, like an employee directory.  I want to maintain this table in it's natural state for other users who may want to query it, but I also want to create pre-built aggregations like "Count of Users by Department" or "Count of Users by State" which are obviously group operations.

Is is possible to create a new table based off of the original table and then apply a grouping to this new table?  In this scenario, I'm anticipating that the data will be loaded or refreshed once by refreshing the orginal table.  Or do I have to perform several Get Data operations and then perform groupings on each table?

Thanks for any hints to get started.

[Greg_Deckler · Accepted solution]

You would only get the table once and then you would create a "Count" measure which could be simply:

Count = COUNTROWS(TableName)

Then, you do your grouping in your visualizations. You could simply create a table visualization and put Department and your Count measure in the table and you would have your count grouping for Department. Same thing in a different table visualization for State and Count.

So, in your data model, you have a single data (fact) table. In your report, you have 2 table visualizations.

[ChrisWilliams]

Thanks, this helped me to get started.  I actually tried creating a measure something like:

ByDepartmentMeasure = COUNTA(MyDepartmentColumn)

In my test, this seemed to work out also.  I'm going to do another test when I get a larger data set.  Do you think this approach creates a similar result to what you're suggesting?

[Eric_Zhang]

@ChrisWilliams

It should work.

By the way, maybe you're a little misunderstanding Smoupre's advice? To get the headcount, you just need a measure as

HeadCount = DISTINCTCOUNT('Employees'[user])

It would be aggregated according to the given aggregating level.