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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

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

 

2 REPLIES 2
johnbasha33
Super User
Super User

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

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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.