I am trying to filter a table by a variable called OnlyTickersWithValues which is a list of the Tickers in the table below that don't have a value of 0. I have a table called Custom_Mix_Tbl see below...
Name Ticker Archetype
| Russell 1000 Growth | ^RLG | 0.00% |
| Russell 1000 Value | ^RLV | 0.00% |
| Russell 2000 | ^RUT | 0.00% |
| Russell 2000 Growth | ^RUO | 0.00% |
| Russell 2000 Value | ^RUJ | 0.00% |
| S&P 500 | ^GSPC | 0.00% |
| Bloomberg Barclays Agg | AGG | 0.00% |
| 13 Week Treasury Bill | ^IRX | 0.00% |
| Treasury Yield 5 Year | ^FVX | 0.00% |
| CBOE Interest Rate 10 Year T No | ^TNX | 0.00% |
| Treasury Yield 30 Year | ^TYX | 0.00% |
| Vanguard S&P 500 Index Fund | VOO | 30.00% |
| iShare Core S&P 500 | IVV | 0.00% |
| iShare Russell 1000 Growth | IWF | 10.00% |
| iShare Russell 1000 Value | IWD | 10.00% |
| iShare Russell 2000 | IWM | 5.00% |
| iShare Russell 2000 Growth | IWO | 5.00% |
| iShare Russell 2000 Value | IWN | 10.00% |
| iShare Russell Midcap | IWR | 12.00% |
| iShare MSCI Emerging | EEM | 0.00% |
| iShare Core MSCI Emerging | IEMG | 8.00% |
| iShare MSCI EAFE | EFA | 0.00% |
| iShare Core MSCI EAFE | IEFA | 10.00% |
| BitCoin | BTC | 0.00% |
| Ethereum | ETH | 0.00% |
I am creating another table called Custom_Mix_Annual_Return with calculated columns to calculates the weighted return of each portfolio mix. Below is the DAX. I am trying to create another variable called CountHistorical that filters another Table called Appended_Historical_Annual_Returns by the Year and the variable OnlyTickersWithValues, however this is giving an error. I am then comparing the count of the rows and if they are equal the return the result if not then Blank(). The rest of the DAX seems to be working correctly. How to do I incorporate the list created by OnlyTickersWithValues into the variable CountHistorical? There are no syntax errors, the error I get says somthing like there is multiple values when it expects 1 and it's pointing at the OnlyTickersWithValues within the CountHistorical variable.
=
VAR CurrentYear = Custom_Mix_Annual_Return[Year]
VAR TickersWithValues = FILTER (Custom_Mix_Tbl,[Archetype] <> 0)
VAR OnlyTickersWithValues=SUMMARIZE(FILTER (Custom_Mix_Tbl, [Archetype] <> 0),Custom_Mix_Tbl[Ticker])
VAR CountCustomMix= Countrows(TickersWithValues)
VAR CountHistorical= Countrows(Filter(filter(Appended_Historical_Annual_Returns,Appended_Historical_Annual_Returns[Year]=CurrentYear),Appended_Historical_Annual_Returns[Ticker]=OnlyTickersWithValues))
RETURN
IF(CountCustomMix<>CountHistorical,Blank(),
SUMX (
TickersWithValues,
LOOKUPVALUE (
Appended_Historical_Annual_Returns[Annual Return with Dividend],
Appended_Historical_Annual_Returns[Ticker],
Custom_Mix_Tbl[Ticker],
Appended_Historical_Annual_Returns[Year],
CurrentYear
)
* [Archetype]
)
)
