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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Abhaykumar
Microsoft Employee
Microsoft Employee

Converting SQL count query to DAX query

I have the following query in SQL:

 

SELECT 
	COUNT(a.CurrentVal) / b.[Count] AS NormalizedCount
	,a.[HType]
	,a.[YearW]
	,a.[OS]
FROM TableA AS a
INNER JOIN TableB AS b
ON a.HType = b.HType AND a.OS = b.OS AND a.YearW = b.YearW
GROUP BY a.YearW, a.HType, a.OS, b.[Count]
ORDER BY a.YearW

 

I wanted to create a measure/column/table in PowerBI Desktop for the above query after importing the two tables. I have explored the Summarize function but I dont seem to get it completely. Also, count function of DAX only counts the date or whole number. What would be a query to get the data for count using group by as indicated by the above query? Help on this is greatly appreciated.

1 ACCEPTED SOLUTION

If you want to return the same table that your SQL-table returns, this might be easier to do in the query editor.

Easiest would actually be to pass that SQL-statement into the optional SQL-statement box when you connect to your server:

PBI_UseSQLStatement.png

 

But this is normally not a good practice, as your query probably wouldn’t fold then.

 

So if your query is too slow, you should use the M-code (that is produced when you use the UI in the query editor).

Advantage here is that you can connect on multiple fields, so no need to create a concatenated column.

 

In your example it would go like this:

  • In TableA check the columns you want to group upon – then Transform -> Group By. As the columns to be grouped by will already be preselected, and the default-action below is already set to COUNT, there’s actually nothing more to do here than to press OK.
  • Next: Home -> Combine -> Merge Queries: Select TableB and check all key-columns (in the same order). OK and click on arrows in header and expand column [Count].
  • Then add column that does calculate the Normalized Count: Add Column –> [Count]/[Count.1] (or whatever the name of this column is then)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
MattAllington
Community Champion
Community Champion

You can't join on multiple columns in Power Pivot. You need a unique key on your dim table and a matching key in the fact table. Try importing the 2 tables and creating a unique key in your load queries by concatenating the columns. Then create a 1 to many join on the key. 

 

Once the tables are loaded and joined, then please clarify the measure requirement 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

If you want to return the same table that your SQL-table returns, this might be easier to do in the query editor.

Easiest would actually be to pass that SQL-statement into the optional SQL-statement box when you connect to your server:

PBI_UseSQLStatement.png

 

But this is normally not a good practice, as your query probably wouldn’t fold then.

 

So if your query is too slow, you should use the M-code (that is produced when you use the UI in the query editor).

Advantage here is that you can connect on multiple fields, so no need to create a concatenated column.

 

In your example it would go like this:

  • In TableA check the columns you want to group upon – then Transform -> Group By. As the columns to be grouped by will already be preselected, and the default-action below is already set to COUNT, there’s actually nothing more to do here than to press OK.
  • Next: Home -> Combine -> Merge Queries: Select TableB and check all key-columns (in the same order). OK and click on arrows in header and expand column [Count].
  • Then add column that does calculate the Normalized Count: Add Column –> [Count]/[Count.1] (or whatever the name of this column is then)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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