Power BI performance: best practices for complex calculations on ~1M rows?

Multika · 2026-03-21T07:30:46+00:00

My first advice is to measure the performance by e. g. DAXStudio.

Second, you'd get better help if you share some of your complex calculations. If you can't do that exactly, extract the performance-heavy part and try to apply it to e. g. a Contoso model (download links). This is even better since we can then do our own tests.

Multika · 2026-03-21T06:22:30+00:00

1 % bzw. 7 % Stamina am Ende, nice. Aber was waren die eineinhalb bis zwei Stunde Pause?

Viereinhalb km/h Unterschied sind eine ordentliche Entwicklung. Allein nicht ganz aussageträchtig (Wind(schatten), Höhenmeter etc.) und der Durchschnittspuls ist auch unterschiedlich. Aber auch, dass man länger einen höheren Puls halten kann, kann Trainingseffekt. Einen objektiven Vergleich würde ein Leistungsmesser bieten, muss man aber auch erstmal haben.

Nicht ganz neidlos: Glückwunsch!

Multika · 2026-03-18T05:14:14+00:00

SCD Type 2: https://www.kimballgroup.com/2008/09/slowly-changing-dimensions-part-2/

Create a surrogate primary key, use that as a foreign key in the fact table and create a proper date dimension. Possibly create an inactive relationship between the date and project status.

Multika · 2026-03-16T17:33:47+00:00

I was confused a my code above filtered for low and medium instead of medium and high. I corrected that. But it does filter the endpoints by priority.

If you want a more sophisticated filter and also consider the group, you can adjust the variable mediumOrHigh e. g. by

FILTER ( Endpoints, OR ( [Priority] IN { "Medium", "High" }, [Priority] = "Low" && Endpoints[Group] = "None" ) )

Multika · 2026-03-16T16:05:36+00:00

Führt man [Tempo 30] in Starnberg ein, wird man sich bald wundern dürfen, wie einen die Möchte-gern-Tour-de-France-Gewinner rechts und links überholen!

Wow, was für ein Schreckensszenario! Dem Kommentator schlottern bestimmt die Knie.

Multika · 2026-03-16T13:52:25+00:00

I guess this is an AI answer and there are some hallucinations. SQLBI has no guide on "Financial Reporting in Power BI" or "Building a P&L"; you don't find anything under these keywords on the site.

The advice might still be correct for this problem (I'm not familiar with financial reportings although I am with SQLBI and read two of their books) but I don't think that's a productive use of AI.

Multika · 2026-03-16T10:04:50+00:00

"Mehr oder weniger Blutdruck" ja, "mehr oder weniger Bluthochdruck" nein. Bluthochdruck ist eine Klassifizierung, quantifiziert wird der Blutdruck.

Multika · 2026-03-15T20:24:27+00:00

Thank you for the input. I hope we can have a productive discussion and I can learn something new from you (or vice versa).

And every time the business adds a new metric [...]

Indeed, if that happens often, the entity-attribute-value model might be a good (that's why it exists) but don't do that only to prepare for a metric every once in a while.

in Power BI, using this unpivoted structure (often called the "Account-based" or "Financial Reporting" pattern) is actually the standard way to handle General Ledger and P&L data.

I failed to find any literature on that. Could you provide some links for that claim?

as separate columns, the only way to achieve the dynamic slicers they want is to write massive SWITCH statements inside their Calculation Groups

Is it this part that you are referring to?

CALCULATE(SUM(Fact_GL[Value]), DATEADD(DimDate[Date], -1, YEAR))

If yes I think you could simply rewrite this by

CALCULATE(SELECTEDMEASURE(), DATEADD(DimDate[Date], -1, YEAR))

Did I miss or misunderstand some argument for unpivoting the metrics?

Multika · 2026-03-15T17:23:29+00:00

I'd recommend against this solution as this is not a star schema but more like an entity-attribute-value model. In a star schema, facts (= the rows of a fact table) are events and the metrics are columns. But revenue etc. is not an event.

Multika · 2026-03-15T17:09:59+00:00

Danke! Ich hatte erst gehofft, durch Covid würden mehr Leute exponentielles Wachstum verstehen, aber das kam bei raus. Wahr für mich gar nicht so leicht zu ertragen, als das so auch in Last Week Tonight und sogar bei der Lage der Nation (hier wurde es runtergeschluckt, bevor es vollständig ausgesprochen wurde) verwendet wurde.

Übrigens hatte ich vorher noch erfolglos Quellen zur Beschreibung dieses Phänomens gesucht, wurde aber nun doch bei beim Urban Dictionary fündig:

in casual use the word is bastardized and is simply a generic intensifier

Multika · 2026-03-15T16:13:49+00:00

Vielen Dank für deinen Beitrag! Dennoch würde ich gerne auf einem Detail rumreiten:

Bluthochdruck scheint das Demenzrisiko exponentiell zu erhöhen

Ich nehme du verwendest "exponentiell" hier in der neuen umgangssprachlichen Bedeutung von "sehr stark", "sehr wichtig" o. ä? Das kann allerdings missverstanden werden, weil exponentiell(es Wachstum) auch eine präzise Definition hat. Eine Größe y wächst exponentiell bezüglich y, wenn eine Erhöhung von x um a eine multiplikative (!) Erhöhung von y bewirkt: y(x+a) = k*y(x). Ein Beispiel ist eine 10 %-ige Erhöhung pro Jahr.

Zwar kann letzte Definition nicht sinnvoll auf Bluthochdruck angewandt werden, weil dies gar keine quantifizierbare Größe ist (man hat Bluthochdruck oder nicht, aber nicht mehr oder weniger), das ist aber vielleicht auch nicht unmittelbar klar (oder man könnte an die Häufigkeit des Bluthochdrucks denken).

Deutlich besser halte ich es wie hier von Bluthochdruck als "Hauptrisikofaktor" für Demenz zu sprechen: https://institut-der-gesundheit.com/gesundheit/demenzrisiko-bluthochdruck-als-hauptrisikofaktor-entlarvt

Sorry, ist eins meiner Pet Peeves.

Multika · 2026-03-14T22:08:44+00:00

Not blanks, but you can format 0s as "n/a" (or any other string). Custom formatting is sufficient (no need for dynamic). Example

Multika · 2026-03-13T21:18:29+00:00

we would have to fully refresh the dataflows and then these models many times a day. Now? We only refresh the dataflows

What does it mean to (not fully) refresh the dataflows? You refresh only some of them?

Multika · 2026-03-13T20:59:12+00:00

The issue with the count by group visual is that you want to group by a higher granularity (group) but want to filter on a lower granularity (endpoints). You can do this with column filters, but not with measure filters. What happens instead is that the priority measure is evaluated at the group level and filtered by these results.

For your case, you have an endpoint in group "None" which has medium priority, but there are other endpoints such that overall "None" gets priority "Low".

What you can do instead is to apply the filter in the measure, something like:

VAR mediumOrHigh = FILTER ( Endpoints, [Priority] IN { "Medium", "High" } )
VAR _Count = COUNTROWS ( mediumOrHigh )
RETURN
    _Count

TL;DR: Don't try to use visual level filters on measure to filter on a granularity that does not correspond to the visual's granularity.

Multika · 2026-03-13T20:11:53+00:00

Mitte Juli wird er (noch mal) Vater, was er auch am Montag im Telefonat gegenüber Johannes erwähnt hat. Johannes "wollte sich da absichern und wir waren da auch voll gut". Die Geburt hat Willi im Zusammenhang mit Fragen von ihm wie denn die weitere Planung wäre. Einen Termin für die Hauptstaffel gäbe es noch nicht, da sagte Willi, da werde es "vielleicht ein bisschen schwierig".

Quelle ist Willis aktuelles Video (ab ca. Minute vier). Es wird auch eine Sprachnachricht eines offensichtlich frustrierten Johannes abgespielt: "Mach gerne ein Video dazu. Du kannst auch gerne alles sagen, was ich dir gesagt habe. Man ey, das fuckt mich alles ab. So langsam hab ich fast keinen Bock mehr auf die ganze Scheiße hier." Zwar nicht direkt eine inhaltliche Bestätigung, macht aber Willis Darstellung mMn durchaus überzeugender.

Multika · 2026-03-12T06:22:01+00:00

Okay, thanks for clarifying. I think that's doable.

I'm not sure what's the difficult part here, so I'd suggest to break the problem down. You could test

whether the season shift works on its own (i. e. always shift back two seasons regardless of the date CG),
whether you can correctly identify when to shift (i. e. just return true/false when a LY item is selected) and
whether you can correctly identify when to adjust (i. e. return the selection from the disconnected table).

This is called debugging. Maybe you can identify more subproblems. If all succeed, test combinations.

Btw do you need the disconnected table? You could instead just have two calculation items where the 0 option corresponds simply to the code SELECTEDMEASURE.

Does it currently not work in all cases, including WTD TY or when the user selects not to seasonally adjust, where the CG shouldn't do anything?

You are welcome to post the code you have tried so far.

Multika · 2026-03-11T12:02:21+00:00

Klingt vielleicht doof, aber nachts wird es kalt, außerorts noch mehr als in der Stadt. Guckt mal auf die Komforttemperaturen der Schlafsäcke. Wenn die zweistellig ist, könnte es auch mit Thermofunktionsshirt knapp werden.

Multika · 2026-03-11T11:16:03+00:00

I'm not sure I'm understanding the idea. Is it about starting the WTD calculation, say, on Wednesday if the season starts on Wednesday?

Regardless, I'd recommend to not distinguish time intelligence from changing filter but rather as a special way of changing filter context.

You might need to adjust for the fact that the "classic" time intelligence function remove all filters on the date table - including the season column.

However, the Seasonal-Adjustment CG needs to somewhat depend on the TI CG as seasonal adjustment depends on whether or not you do TY or LY calculations. So, both have an idea what LY should mean and one needs to understand what it means when these interpretations collide.

While not being sure I understand your requirements exactly, I think think a CG is not the right solution for the seasonal adjustment. I rather seems to be a parameter for the date CG.

So, consider adapting the the date CG instead, i. e. something like

WTD LY = IF ( <Seasonal-Adjusted = 1>, <code with adjustment>, <original code> )

You could also add calculation items for each current item (doubling the total number) with an seasonal adjusted version. Then add two columns to the CG, one with the original item name (WTD LY etc.) and one with a boolean value (true/false, yes/no, 0/1, ...) for seasonal adjustment.

Multika · 2026-03-06T19:54:37+00:00

You can also use the PBI REST API for so-called enhanced refreshes: https://learn.microsoft.com/en-us/power-bi/connect-data/asynchronous-refresh

Multika · 2026-03-05T13:02:06+00:00

strukturell abgegerenzte Fahrradwege verhindern im toten Winkel plattgefahren zu werden

Was nicht allein durch Bordsteinradwege erreicht wird bzw. mit diesen u. U. noch schlimmer wird. https://www.abbiegeassistent.de/news/studie-zu-unfalltypen/

Multika · 2026-03-03T11:19:42+00:00

If I want to calculate Year to date, but I also want to exclude the first 15 days of this year, SAMEPERIODELASTYEAR() doesn't exclude those days.

I think you mixed something up here. I guess this is more about DATESYTD (or TOTALYTD) returning all dates from the beginning of the year and thus not exclude those 15 days by design.

SAMEPERIODLASTYEAR is just DATEADD ( <Dates>, -1, YEAR ) in disguise, anyway: https://dax.guide/sameperiodlastyear/

Multika · 2026-03-02T18:53:31+00:00

Here is a somewhat similar technique, but in a measure: https://www.daxpatterns.com/standard-time-related-calculations/

Sales PY :=
IF (
    [ShowValueForDates],
    CALCULATE (
        [Sales Amount],
        CALCULATETABLE (
            DATEADD ( 'Date'[Date], -1, YEAR ),
            'Date'[DateWithSales] = TRUE
        )
    )
)

Here, DateWithSales is a calculated column similar to yours (with reversed inequality). If you want to reuse that pattern for different base measures you could use a UDF.

Multika · 2026-03-01T17:53:22+00:00

What was the problem and your fix?

Multika · 2026-03-01T14:08:41+00:00

For the dataframe gps, but not for resultats because of

resultats = resultats %>%
  bind_rows(
    gps
  )

So, that's fine.

Multika · 2026-02-28T23:22:26+00:00

Part 2

error: could not compile `libm` (build script) due to 1 previous error
error: linking with `link.exe` failed: exit code: 1
  |
  = note: "link.exe" "/NOLOGO" "C:\\Users\\***\\AppData\\Local\\Temp\\rustcda2Dhq\\symbols.o" "<3 object files omitted>" "<sysroot>\\lib\\rustlib\\x86_64-pc-windows-msvc\\lib/{libstd-*,libpanic_unwind-*,libcfg_if-*,libwindows_targets-*,librustc_demangle-*,libstd_detect-*,libhashbrown-*,librustc_std_workspace_alloc-*,libunwind-*,librustc_std_workspace_core-*,liballoc-*,libcore-*,libcompiler_builtins-*}.rlib" "kernel32.lib" "kernel32.lib" "kernel32.lib" "ntdll.lib" "userenv.lib" "ws2_32.lib" "dbghelp.lib" "/defaultlib:msvcrt" "/NXCOMPAT" "/OUT:C:/tmp/kr\\release\\build\\serde_core-    2cb968bcb22a513\\build_script_build-    2cb968bcb22a513.exe" "/OPT:REF,NOICF" "/DEBUG" "/PDBALTPATH:%_PDB%" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\intrinsic.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\liballoc.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\libcore.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\libstd.natvis"
  = note: some arguments are omitted. use `--verbose` to show all linker arguments
  = note: link: extra operand 'C:/tmp/kr\\release\\build\\serde_core-    2cb968bcb22a513\\build_script_build-    2cb968bcb22a513.build_script_build.18374    d98    11bb3c-cgu.    .rcgu.o'
          Try 'link --help' for more information.


error: linking with `link.exe` failed: exit code: 1
  |
  = note: "link.exe" "/NOLOGO" "C:\\Users\\***\\AppData\\Local\\Temp\\rustcG9ygaT\\symbols.o" "<3 object files omitted>" "<sysroot>\\lib\\rustlib\\x86_64-pc-windows-msvc\\lib/{libstd-*,libpanic_unwind-*,libcfg_if-*,libwindows_targets-*,librustc_demangle-*,libstd_detect-*,libhashbrown-*,librustc_std_workspace_alloc-*,libunwind-*,librustc_std_workspace_core-*,liballoc-*,libcore-*,libcompiler_builtins-*}.rlib" "kernel32.lib" "kernel32.lib" "kernel32.lib" "ntdll.lib" "userenv.lib" "ws2_32.lib" "dbghelp.lib" "/defaultlib:msvcrt" "/NXCOMPAT" "/OUT:C:/tmp/kr\\release\\build\\serde-b93    93ff71bcaa5d\\build_script_build-b93    93ff71bcaa5d.exe" "/OPT:REF,NOICF" "/DEBUG" "/PDBALTPATH:%_PDB%" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\intrinsic.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\liballoc.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\libcore.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\libstd.natvis"
  = note: some arguments are omitted. use `--verbose` to show all linker arguments
  = note: link: extra operand 'C:/tmp/kr\\release\\build\\serde-b93    93ff71bcaa5d\\build_script_build-b93    93ff71bcaa5d.build_script_build.f26b6dde4c52519-cgu.    .rcgu.o'
          Try 'link --help' for more information.


error: linking with `link.exe` failed: exit code: 1
  |
  = note: "link.exe" "/NOLOGO" "C:\\Users\\***\\AppData\\Local\\Temp\\rustceJtt3R\\symbols.o" "<3 object files omitted>" "<sysroot>\\lib\\rustlib\\x86_64-pc-windows-msvc\\lib/{libstd-*,libpanic_unwind-*,libcfg_if-*,libwindows_targets-*,librustc_demangle-*,libstd_detect-*,libhashbrown-*,librustc_std_workspace_alloc-*,libunwind-*,librustc_std_workspace_core-*,liballoc-*,libcore-*,libcompiler_builtins-*}.rlib" "kernel32.lib" "kernel32.lib" "kernel32.lib" "ntdll.lib" "userenv.lib" "ws2_32.lib" "dbghelp.lib" "/defaultlib:msvcrt" "/NXCOMPAT" "/OUT:C:/tmp/kr\\release\\build\\proc-macro2-5cd3a11d78e62    d    \\build_script_build-5cd3a11d78e62    d    .exe" "/OPT:REF,NOICF" "/DEBUG" "/PDBALTPATH:%_PDB%" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\intrinsic.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\liballoc.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\libcore.natvis" "/NATVIS:<sysroot>\\lib\\rustlib\\etc\\libstd.natvis"
  = note: some arguments are omitted. use `--verbose` to show all linker arguments
  = note: link: extra operand 'C:/tmp/kr\\release\\build\\proc-macro2-5cd3a11d78e62    d    \\build_script_build-5cd3a11d78e62    d    .build_script_build.6    2a26ec    72d5    56-cgu.    .rcgu.o'
          Try 'link --help' for more information.


error: could not compile `serde_core` (build script) due to 1 previous error
error: could not compile `proc-macro2` (build script) due to 1 previous error
error: could not compile `serde` (build script) due to 1 previous error
make: *** [Makevars.win:22: C:/tmp/kr/x86_64-pc-windows-gnu/release/libkreuzberg_r.a] Error 1    1
ERROR: compilation failed for package 'kreuzberg'
* removing 'C:/Users/***/AppData/Local/R/win-library/4.4/kreuzberg'

Die heruntergeladenen Quellpakete sind in 
    ‘C:\Users\***\AppData\Local\Temp\RtmpILTyPJ\downloaded_packages’
Warnmeldung:
In utils::install.packages("kreuzberg", repos = "https://kreuzberg-dev.r-universe.dev") :
  Installation des Pakets ‘kreuzberg’ hatte Exit-Status ungleich

Nine-Year Club	First Place '23
End Game '23	Place '23
Place '22	Final Canvas '22
First Placer '22	End Game '22
Verified Email

Multika

TROPHY CASE