DirectQuery group by/aggregates

[Anonymous]

I think the following may be an obvious question but I'm continuing to run into the "we couldn't fold the expression to the data source" error.

I have a DirectQuery table (call that X_DETAIL), it's linked via a column, let's call that `id` to a summary table (Y_SUMMARY) that I'm using to drive my visualization.

I want to be able to show distinct counts of one column (call that EMAIL_ID) on X_DETAIL and I want this to be filterable and groupable by ID and also columns A and B on X_DETAILS

So let's say the user selects B= "Email Delivered" and they have also selected ID = 12345

the equivalent SQL query would be

SELECT a, count(distinct email_id) from X_DETAIL where id = 12345 and b = "Email Delivered" group by a

I've tried creating a table with

SUMMARIZE(X_DETAILS, X_DETAILS['a'], "Email Count", DISTINCTCOUNT(X_DETAILS['email_id']))

,and this gives me "we couldn't fold the expression to the data source" error. (I realize this table probably needs filters on it as well, but even this simple version doesn't work)

I've also tried various versions of measures as well and I get the same result. 

I'm using Snowflake. 

Am I missing something obvious?

[Anonymous · Accepted solution]

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. 

[bhelou]

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.

[Anonymous]

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.