Forum Discussion
Microsoft EmployeeConverting 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.
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:
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)
2 Replies
- MattAllington
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
- ImkeF
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:
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)
