slow perfomance summarize 15 minutes

[wilmadmax]

Hi

Im new in DAX, and trying to figure out why the performance below its so slow

my database has about 4.200 (million lines) to be analyzed, but im using slicers by month, this reduce to about 400.000 lines to be analyzed each month, and the columns im summarizing(inss chave, cpf, categoria esocial) wil produce about 3800 lines at the end. The the pivot table that resumes this measure has only 20 lines by month.

every time i choose new month in slicer the time to fulfill my pivot table is about 15 minutes, yes you saw right, 15 minutes to fulfiil
i was thinking that i dont need sumx, because the filter and summarize it produces what i need, but im using power pivot and not power bi, and power pivot i cannot start the measure with filter and summarize, this show me an error:
"multiple columns can not be converted to a scalar value"


=SUMX(
FILTER(
SUMMARIZE(
TAB_VALORES;
[INSS CHAVE];
[CPF];
[CATEGORIA ESOCIAL];
"TAB_ITBF";
CALCULATE(
SUM(
TAB_VALORES[EVENTO VALOR]);
TAB_EVENTOS[IRRF INCIDÊNCIA CÓDIGO]=13;
TAB_EVENTOS[EVENTO TIPO]="V")-
CALCULATE(
SUM(
TAB_VALORES[EVENTO VALOR]);
TAB_EVENTOS[IRRF INCIDÊNCIA CÓDIGO]=13;
TAB_EVENTOS[EVENTO TIPO]="D"));
[TAB_ITBF]>0);
[TAB_ITBF])

[MattAllington]

I haven’t read you question in detail, but let me make an observation. I have a saying: If you come from a database background, every DAX problem looks like a DAX query. But the truth is, Power BI and DAX are not the same as a traditional database. In power BI, you should be modelling your data with a star Schema (if possible) and then use the DAX formula language to aggregate your data and use the UI to write the queries for you. It is possible that a measure including summarize is a good measure, but the truth is that more than 95% of these that i have seen are basically bad DAX. I don’t know if your measure is good or bad - I can’t tell. I can’t see the table layout and I can’t be sure that you are using best practice naming conventions. Let me ask you a question. What are you trying to do, and what does your data model look like?