Forum Discussion
Directquery limitation summarize
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
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