Forum Discussion
DirectQuery group by/aggregates
I did figure this out in the end, though I didn't find a way to write SQL directly to the DB to connect via DirectQuery, I had to use the GroupBy functionality in the data view. The other solution is for me to actually write a table that does the summaries for me in the database, but what I have works now.
Hi ,
One approach is to use the GROUP BY clause in SQL directly to group the rows by the "a" column and then use COUNT(DISTINCT email_id) to count the distinct values of the "email_id" column. You can then create a DirectQuery measure in Power BI that uses this SQL query directly. Here's an example measure:
Email Count =
COUNTROWS(
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
'X_DETAIL',
'X_DETAIL'[id] = SELECTEDVALUE('Y_SUMMARY'[id]) &&
'X_DETAIL'[B] = SELECTEDVALUE('Y_SUMMARY'[B])
)
),
"a", 'X_DETAIL'[a],
"email_count", CALCULATE(COUNT(DISTINCT 'X_DETAIL'[email_id]))
)
)
/////
This measure uses the FILTER function to filter the rows in the "X_DETAIL" table based on the selected "id" and "B" values from the "Y_SUMMARY" table. It then uses the KEEPFILTERS function to preserve any filters applied to the visual. The SELECTCOLUMNS function is used to return a table with two columns: "a" and "email_count". Finally, the COUNTROWS function is used to count the rows in the resulting table, which represents the distinct count of "email_id" values grouped by the "a" column.
Note that you may need to modify the syntax of the SQL query depending on the dialect of SQL supported by Snowflake.
Thanks! I still can't get this to work though.
What do you mean by I may need to modify the syntax of the SQL query, given this is DAX - how to I understand how this would be translated to underlying SQL?
Also
CALCULATE(COUNT(DISTINCT 'X_DETAIL'[email_id])
in your query does not seem like valid DAX, and I had to modify it (I tried DISTINCTCOUNT instead)
And can you explain what you mean by:
One approach is to use the GROUP BY clause in SQL directly to group the rows by the "a" column and then use COUNT(DISTINCT email_id) to count the distinct values of the "email_id" column. You can then create a DirectQuery measure in Power BI that uses this SQL query directly.
Can I write SQL to connect to my table directly? I'm very new to DAX so I think I might be confused.