CODEWITHSUNDEEP
powerbidaxpowerquery
YK_NL
YK_NL

Reputation: 17

how to create a DAX measure / power query process / which returns a value from another row, which has the same date & time

Please find below links to the Fact table and the overview of all tables. I would like to create a DAX measure, or new column in the Fact table ("transactions"), where:

In my Fact table screenshot, I manually added the EUR_amt column in Excel to show what I would like to create

I think it's also possible to add the column, then group by time and date, such that the rows with EUR as currency with EUR_amt being 0, would be removed. All using power query. That would be even better.

(The "Currencies" table just uses the distinct values of the "currency" column in the "transactions" table, via PowerQuery. Not relevant for this question I think)

Many thanks in advance!

-YK

Fact table "transactions"
Fact table "transactions"

Overview of tables
Overview of tables

Upvotes: 1

Views: 180

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

Here's one way to do this using just Power Query and the Advanced Editor

  • Group by data and time
  • Generate a custom column for each subtable based on your rules
  • Expand the subtables, remove those with "0", and re-order the columns
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"type", type text}, {"currency", type text}, {"Date", type date}, {"Time", type time}, {"amount", type number}}),

//Group by Date and Time
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Time"}, {

//Add the Eur amt column to the grouped table
        {"EUR_amt", (t)=>let 

//determine relevant euro amt for each sub table
                eur= t[amount]{List.PositionOf(t[currency],"EUR")},

//add the column to each subtable basaed on conditions
                addCol = Table.AddColumn(t, "EUR_amt", 
                    each if [type]= "trade" and [currency]<>"EUR" then -eur else 0)
            in 
                addCol}}),

//Expand the new table
//Filter out the 0's
//reorder the columns
    #"Expanded EUR_amt" = Table.ExpandTableColumn(#"Grouped Rows", "EUR_amt", {"type", "currency", "amount", "EUR_amt"}, {"type", "currency", "amount", "EUR_amt"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded EUR_amt", each ([EUR_amt] <> 0)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"type", "currency", "Date", "Time", "amount", "EUR_amt"})
in
    #"Reordered Columns"

enter image description here

Upvotes: 1

Jos Woolley
Jos Woolley

Reputation: 9062

Calculated Column:

EUR_amt =
IF (
    OR ( transactions[type] <> "trade", transactions[currency] = "EUR" ),
    0,
    - LOOKUPVALUE (
        transactions[amount],
        transactions[Date], transactions[Date],
        transactions[Time], transactions[Time],
        transactions[currency], "EUR",
        0
    )
)

Upvotes: 2

Related Questions