topic Re: Convert Dax Formula to Power Query in Desktop

Convert Dax Formula to Power Query

dirkkoch — Thu, 11 Apr 2024 11:01:53 GMT

I have the following DAX formula which is working fine in my PBI-report but now I want to create a custom column within a dataflow and I have to use Power Query language. Can someone convert this DAX-formula to Power Query language so that it will work identical?!

Unfallfrei_Zähler =

VAR AktuellesDatum = 'PEL Unfallkreuz'[Datum]

VAR LetzterUnfallTag = CALCULATE(MAX('PEL Unfallkreuz'[Datum]),

'PEL Unfallkreuz'[PELA.Unfallfreie Tage] = 0 && 'PEL Unfallkreuz'[Datum] <= AktuellesDatum)

VAR ErsterUnfallfreierTag = MIN('PEL Unfallkreuz'[Datum])

RETURN

IF(ISBLANK(LetzterUnfallTag), DATEDIFF(ErsterUnfallfreierTag, AktuellesDatum, DAY), DATEDIFF(LetzterUnfallTag, AktuellesDatum, DAY))

Re: Convert Dax Formula to Power Query

johnbasha33 — Thu, 11 Apr 2024 16:49:28 GMT

dirkkoch

You can achieve similar functionality in Power Query by creating a custom column. However, please note that Power Query doesn't support variables like DAX, so you'll need to work with nested functions instead. Here's the equivalent Power Query code:

```m
let
AktuellesDatum = [Datum],
LetzterUnfallTag = List.Max(
Table.SelectRows(#"PEL Unfallkreuz", each [PELA.Unfallfreie Tage] = 0 and [Datum] <= AktuellesDatum)[Datum]),
ErsterUnfallfreierTag = List.Min(Table.SelectRows(#"PEL Unfallkreuz", each [PELA.Unfallfreie Tage] > 0)[Datum]),
DaysDifference =
if LetzterUnfallTag = null then
Duration.Days(ErsterUnfallfreierTag - AktuellesDatum)
else
Duration.Days(LetzterUnfallTag - AktuellesDatum)
in
DaysDifference
```

This Power Query code calculates the number of days between the current date (`AktuellesDatum`) and either the last accident date (`LetzterUnfallTag`) or the first accident-free date (`ErsterUnfallfreierTag`). The result is then returned as the custom column value `DaysDifference`.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Re: Convert Dax Formula to Power Query

dirkkoch — Fri, 12 Apr 2024 06:29:22 GMT

Thank you for the reply. Unfortunately I receive an error message saying

Expression.Error: During the evaluation a circular reference was detected.

Details

Reason = Expression.Error