Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Aweklin
New Member

How to group data on table

Hello,

 

How do I display Product Categories and Product Count Against each category in a tabular format?

 

Just like writing

 

SELECT c.Name AS Category, Count(p.ID) AS ProductCount

FROM Category c

INNER JOIN Product p ON c.ID = p.CategoryID

GROUP BY c.Name

 

And is it possible to even write SQL Queries in PowerBI? I use to do this in QlikView!

 

Thanks.

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

Hi @Aweklin,

 

Perhaps you can try to use group function of power query.

 

Referecn link:

Group rows in a table (Power Query)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Phil_Seamark
Employee
Employee

Hi @Aweklin

 

If your datasource is an SQL datasource then you can just run this SQL as your query into your table and let the DB Engine do the grouping.  Otherwise you can do this both in the Query Editor and in DAX.

 

In DAX the forumla will look something like :

 

New Table = SUMMARIZE ( '<your table>' , [Name] , "ProductCount" , COUNTROWS('<your table>') )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Phil_Seamark
Employee
Employee

Hi @Aweklin

 

If your datasource is an SQL datasource then you can just run this SQL as your query into your table and let the DB Engine do the grouping.  Otherwise you can do this both in the Query Editor and in DAX.

 

In DAX the forumla will look something like :

 

New Table = SUMMARIZE ( '<your table>' , [Name] , "ProductCount" , COUNTROWS('<your table>') )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

v-shex-msft
Community Support
Community Support

Hi @Aweklin,

 

Perhaps you can try to use group function of power query.

 

Referecn link:

Group rows in a table (Power Query)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
bjnodello
Helper II
Helper II

You should be able to add Category and ID to a table visualization.  On the ID click the little down arrow and select "Count" (Values section of the table element on the right side menu).

 

I'm not familiar with QlikView.  You can use SQL to get your data into Power BI from a source that supports it, but DAX is used to create measures and calculated columns within the model.

 

Brett

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.