Creating a derived table in Power BI

Question

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.

amitchandak · Accepted Answer

PBI5851 , new table
summarize(Table,Table[AccountDate],Table[Client], Table[AccountStatus], "Count",countrows(Table)))
&nbsp;
Also, refer how can work with a table and aggregated Table
&nbsp;
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations
https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table

Forum Discussion

Creating a derived table in Power BI

2 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Creating Date Tables

DERIVATED TABLE