Directquery limitation summarize

[jaime_parra]

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

[v-kongfanf-msft]

Hi @jaime_parra ,

In your DAX expression, you are using SUMMARIZECOLUMNS, which may generate intermediate tables with detailed information for subsequent calculations. In some cases, this can lead to poor performance, especially when working with large datasets.

One way to optimize DAX queries is to use a combination of CALCULATETABLE and VALUES. the idea is to create a filtered table directly in the source and reduce the number of rows before bringing it into Power BI for the final computation.

Try formula like below:

light_users = 
CALCULATE (
    COUNTROWS (
        VALUES ( canales[users] )
    ),
    FILTER (
        ALL ( canales ),
        canales[Date] >= DATE ( 2023, 10, 1 ) &&
        canales[Date] <= DATE ( 2023, 11, 5 ) &&
        AVERAGE ( canales[transactions] ) >= 15 &&
        AVERAGE ( canales[transactions] ) <= 25
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jaime_parra]

Hi @v-kongfanf-msft 

I have made the corresponding tests and different variations in the code. However, the result obtained in the query is blank.

When testing and validating what the code does in the dwh I see that it creates the following:

The problem observed with this metric is:

1. It does not consider the date filter, so the sum and count have them over the entire base.
2. Although it returns two values, in Power BI it does not display them but leaves them blank.
3. It is not applying the average filter either, so even when modifying the metric it is not generating the correct values.

Thank you very much for your collaboration, in case of any other suggestions I will be very attentive.

Regards

[tamerj1]

Hi @jaime_parra 

try without variables

light_users =
COUNTROWS (
FILTER (
SUMMARIZE (
Cancels,
canales[users],
"@avg_trx", AVERAGE ( canales[transactions] )
),
[@avg_trx] >= 15
&& [@avg_trx] <= 25
)
)

[jaime_parra]

Hi @tamerj1 

I told you that I did the test with the DAX you sent me and it still generates the same in the data source. So the problem persists.