Creating a derived table in Power BI

[PBI5851]

I have a table which when used with extensive DAX, i keep getting the "Cannot display visual "as not enough reources available. When checking the analyzer, there is a DAX that is taking up majority of the time. i have sufficient filters to limit the data, but not sure what else is going on. It is referencing a table that has around 2 million rows. I'm assuming that since it has to read that entire table and use the filters, it may be using up the resources. 

My question is can i create a derived table in Power Bi similar to a count(*)/group by in SQL . 

Existing table in power BI

AccountID	AccountDate	AccountStatus	Client	Type
A1234	1/1/2020	Cancelled	East	Shoes
A234	1/1/2020	Shipped	East	Belt
A345	1/1/2020	Shipped	East	Belt
A456	1/5/20	Complete	West	Shoes
A234	1/5/20	Complete	West	Mask
A345	1/8/20	Complete	South	Buckle

Is it possible to create a dynamic table as below within Power BI and use that in the relationship and subsequent visuals , (in sql, select count(*) CountNum, AccountDate, Client, Accountstatus from AccountSales group by Accountdate, Client, AccountStatus)

CountNum	AccountDate	Client	AccountStatus
1	1/1/2020	East	Cancelled
2	1/1/2020	East	Shipped
2	1/5/2020	West	Complete
1	1/8/2020	South	Complete

Any recommendations. 

[v-juanli-msft]

Hi @PBI5851 

If you connect to sql server with import mode, please enter sql statement as below:

Select SubjectId, ExamDate, count(StudentNo) as CountNum From dbo.Result$ Group By SubjectId, ExamDate

Then can limit the rows imported into power bi.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@PBI5851 , new table

summarize(Table,Table[AccountDate],Table[Client], Table[AccountStatus], "Count",countrows(Table)))

Also, refer how can work with a table and aggregated Table

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table