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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sunny_punalkar
Frequent Visitor

Replicating excel formula | precedent referencing

Hello community,

 

Need help in replicating the following formula in DAX or in power query -

 

In Excel we can easily use precedent referencing but in dax or power query I haven't found anything, i need ut for below use, in first screenshot it's the evaluated values, and in second, formulas behind those, i just need a way i can do the same in power bi at the end,

note that first values are manually entered '0'

 

Sunny_punalkar_0-1684398499373.pngSunny_punalkar_1-1684398535974.png

can anyone please help? need this urgently for a very large database
copy paste the values from below to test out 

Total engine hoursPulse
00
0.80.8
1.651.65
0.31.65
0.31.65
6.17.45
1.157.45
1 ACCEPTED SOLUTION

let
    Source = your_source_table,
    data = List.Buffer(Source[Total engine hours]),
    max_count = List.Count(data),
    pulse = 
        List.Generate(
            () => [i = 0, p = 0],
            (x) => x[i] < max_count,
            (x) => [i = x[i] + 1, p = if (data{i} >= data{x[i]}) then x[p] + data{i} - data{x[i]} else x[p]],
            (x) => x[p]
        ),
    out = Table.FromColumns(Table.ToColumns(Source) & {pulse}, Table.ColumnNames(Source) & {"Pulse"})
in
    out

View solution in original post

6 REPLIES 6
AlienSx
Super User
Super User

Hi, @Sunny_punalkar 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    data = List.Buffer(Source[Total engine hours]),
    max_count = List.Count(data),
    pulse = 
        List.Generate(
            () => [i = 0, p = 0],
            (x) => x[i] < max_count,
            (x) => [i = x[i] + 1, p = if (data{i} >= data{x[i]}) then x[p] + data{i} - data{x[i]} else x[p]],
            (x) => x[p]
        ),
    out = Table.FromColumns({data, pulse}, {"Total engine hours", "Pulse"})
in
    out

@AlienSx, this sort of solves my issue, but my actual data comes from sql database which has this 'Total engine hours' with multiple other columns, which of the bits i would need to edit then?

let
    Source = your_source_table,
    data = List.Buffer(Source[Total engine hours]),
    max_count = List.Count(data),
    pulse = 
        List.Generate(
            () => [i = 0, p = 0],
            (x) => x[i] < max_count,
            (x) => [i = x[i] + 1, p = if (data{i} >= data{x[i]}) then x[p] + data{i} - data{x[i]} else x[p]],
            (x) => x[p]
        ),
    out = Table.FromColumns(Table.ToColumns(Source) & {pulse}, Table.ColumnNames(Source) & {"Pulse"})
in
    out

I believe this will definitely help, thank you so much!

Do you know if there's a way we can achieve the same through DAX?

DAX - most likely yes but I am not a DAX guy (yet) 😎 

Pretty sure you will reach there very soon, really appreciate your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors