Converting SQL count query to DAX query

[Abhaykumar]

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.

[MattAllington]

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 · 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:

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)