How to dynamically create a unioned table based on filters?

[dancarr22]

Hello,

We are trying to calculate IRR.  To do so (I think) we need to create a dynamic table based on 2 tables in our data model: transactions and positions.  We need to UNION the most recent position market value with all of the transactions.
We have a SQL function which does this - the simplified logic of which is:

--Position
SELECT Cusip, Date, MarketValue, 'Ending MarketValue' AS TransType 
FROM Position POS
WHERE DataSourceGroup = @DataSourceGroup AND CUSIP = @CUSIP
AND AsOfDate = MAX(AsOfDate)
UNION
--Transaction
SELECT Cusip,  TradeDate, TransAmount,TransType
FROM Transactions
WHERE DataSourceGroup = @DataSourceGroup AND TradeDate <= @AsOfDate AND CUSIP = @CUSIP

Is there any way to do this in DAX?  Then, we would take the result of this and calc IRR against it.  We cannot predefine this table because users may choose different dates and securities/cusips.

Appreciate any help

Thanks,

Dan

[dancarr22]

Thanks @BrianConnelly !  Your solution pushed me in the right direction.  Almost there - just need to work out a few more things.  Appreciate your help!
Dan

[amitchandak]

@dancarr22 , In power query you can use append (delete the duplicate post that if needed)

Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

in DAX you can use union

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[dancarr22]

One additional follow-up.  I did test creating this and noticed when I create a dynamic table (SELECTCOLUMNS) based on another table - the dynamic table does not filter when the main table it is based on is filtered.  How can that be done?  I am referencing Transactions table.  So, I thought my dynamically created table would update when the underlying source for that is filtered - but that is obviously not the case.  How can newTableTEST be updated whenever table Transactions is filtered?  Thanks!

newTableTEST = SELECTCOLUMNS(Transactions, "CUSIP", Transactions[CUSIP (Filter)], "Date", Transactions[TradeDate], "Amount", Transactions[Amount])

[BrianConnelly · Accepted solution]

First, the table you are creating is not dynamic.  It is a summarized table that would load and be static until the next refresh.  You could create a measure based on a summarized table.  For example,

<Measure Name> =

Var  YourSummaryTable = SELECTCOLUMNS(Transactions, "CUSIP", Transactions[CUSIP (Filter)], "Date", Transactions[TradeDate], "Amount", Transactions[Amount])

RETURN SUMX(YourSummaryTable , [Amount])