Forum Discussion
Microsoft EmployeeConverting SQL count query to DAX query
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)
Community ChampionYou 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
Community ChampionIf 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)