Forum Discussion
Nested Filter Referencing Two Unrelated Tables.
Hello everyone,
I am feeling absolutely beat. I have been trying to get a conditional value to populate based on if two conditions are met, which will then be used in future formulas, but apparently I am at a complete lack of understanding as to how PBI operates when it comes to evaluating expressions.
I have 3 tables with Stock Data pulling form a SQL server:
Stock_main - Columns (Stock ID (Primary), Ticker, Company_name, stock_index)
Owned - Columns (Purchase ID, Stock ID (Foreign), Buy_Date, Shares, Cost, Portfolio)
Price - Columns (Stock ID (Foreign), Date_stamp, Open, High, Low, Close, Volume)
What I want is to determine if I have owned a specific stock on that date. I have been trying methods with IF statements and FILTER statements to iterate through each row of the "Price" table to look at the stock ID, match it with a Stock ID from the "Owned" table, then determine if the Date_stamp in the price table is greater than (after) the date i bought it by referencing the "Buy_Date" column in the "Owned" table.
This is one that that works at a hit rate of about 80% of the time.
Like I said, the above formula results in a TRUE or FALSE correctly about 80% of the time.
Please help. There has to be an easier way to do this but I'm just so wrapped up in this incorrect method that I am stuck running in circles.
Cheers,
- You never sell your stock? (Can we assume that if a row has TRUE for one date and stock, it will remain true for the rest of the table for that stock?)
If so, the calculated column (big difference with a measure) is like this (typing on my phone so sorry for mistakes):
VAR curDate= Price[date_stamp]
VAR curStock = Price[Stock_ID]
RETURN
IF ( COUNTROWS( FILTER( ALL(Owned), Owned[Stock_ID]= curStock && Owned[BuyDate] < curDate)) > 0, TRUE, FALSE)
3 Replies
Tip: start using variables to make your code more readable. It took me a while to read your code. Now my biggest question is; is this a calculated column and if so, to what table? You say "I want to determine if I owned a stock on a date", where does that date come from? What has that to do with table Price? (Or does that table only contains rows for OLHC when you owned a stock?)
Please explain a bit more what you are trying to achieve (and where) and wat the expected result looks like 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Djerro,
Sorry for the unclear question, allow me to clarify.
This will be a calculated column, or measure (Im unsure which to be using), within the Price table. The price table holds the the HLOC and volume for each day, for each stock.
Date_Stamp Stock_ID Open High Low Close Volume 1/1/2020 1234 $5 $5.50 $4.90 $5.12 1M
1/1/2020 5678 $100 $101 $98 $100 5M 1/1/2020 789 $40 $45 $40 $45 4M 1/2/2020
1234 $5.12 $5.60 $4.98 $5.45 2M
1/2/2020 5678 $100 $105 $98 $102 6M 1/2/2020 789 $45 $45 $38 $39 10M What I need is a column (either this table, or a new table) where a TRUE or FALSE is populated if I owned that stock on that date. See below, I did not own stock 789 on 1/1 or 1/2. The reason I need this is because I will use this to determine if I will include that stock's value, multiplied by the number of shares I own, in my overall portfolio. What I want is for it to look at the "Date_stamp" column and determine if that date is after the "Buy_date" column, which comes from a different table.
Date_Stamp Stock_ID Owned at Date 1/1/2020 1234 TRUE 1/1/2020 5678 TRUE 1/1/2020 789 FALSE 1/2/2020
1234 TRUE 1/2/2020 5678 TRUE 1/2/2020 789 FALSE Also I found some tips yesterday where people are recommending using a seperate calendar table in order to perform date/time analysis in PBI.
- You never sell your stock? (Can we assume that if a row has TRUE for one date and stock, it will remain true for the rest of the table for that stock?)
If so, the calculated column (big difference with a measure) is like this (typing on my phone so sorry for mistakes):
VAR curDate= Price[date_stamp]
VAR curStock = Price[Stock_ID]
RETURN
IF ( COUNTROWS( FILTER( ALL(Owned), Owned[Stock_ID]= curStock && Owned[BuyDate] < curDate)) > 0, TRUE, FALSE)
