Directquery limitation summarize
Hi, Context: I have a table with 7 billion rows in bigquery, the table is partitioned and clustered and performs well when sending queries. I'm trying to get the number of unique users based on a certain condition and the following SQL syntax works perfectly. SELECT COUNT(DISTINCT users) AS cnt_users FROM ( SELECT users, AVG(transactions) AS transactions FROM `project.dataset.canales` WHERE Date BETWEEN '2023-10-01' AND '2023-11-05' GROUP BY users) WHERE transactions BETWEEN 15 AND 25; The result is one row with the value and the query runs in 2-3 seconds. When translate this query to dax: light_users = VAR _users = SUMMARIZECOLUMNS ( canales[users], "avg_trx", AVERAGE ( canales[transactions] ) ) VAR _filter = FILTER ( _users, [avg_trx] >= 15 && [avg_trx] <= 25 ) RETURN COUNTROWS ( _filter ) The result in power bi is Looking at the query created by Dax in bigquery I can see that Dax created a temp table but the quantity is 1+ million records The table returns to Power BI to make the final calculation. So, How can I calculate all directly in the source and solve the problem with the returned rows? I tried using vars, without vars and nothing works for me. Thanks
