Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III
Helper III

Convert Dax Formula to Power Query

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

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


Solution Sage
Solution Sage


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:

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)
Duration.Days(LetzterUnfallTag - AktuellesDatum)

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

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

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

Reason = Expression.Error


Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors