Skip to main content
cancel
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

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
Solution Sage
Solution Sage

@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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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