topic Re: Directquery limitation summarize in DAX Commands and Tips

Directquery limitation summarize

jaime_parra — Fri, 05 Jan 2024 16:34:34 GMT

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

Re: Directquery limitation summarize

tamerj1 — Fri, 05 Jan 2024 17:14:46 GMT

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
)
)

Re: Directquery limitation summarize

jaime_parra — Fri, 05 Jan 2024 17:20:51 GMT

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.

Re: Directquery limitation summarize

Anonymous — Tue, 09 Jan 2024 06:49:38 GMT

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

Re: Directquery limitation summarize

jaime_parra — Wed, 10 Jan 2024 13:54:36 GMT

Hi Anonymous

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:

It does not consider the date filter, so the sum and count have them over the entire base.
Although it returns two values, in Power BI it does not display them but leaves them blank.
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