Forum Discussion
Helper VHow to dynamically create a unioned table based on filters?
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
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])
4 Replies
dancarr22One 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])
BrianConnellyResolver III
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])
- amitchandak
Super User
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-queryin DAX you can use union
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
- 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
