Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
shoof
Regular Visitor

Using a Table Variable to Filter another Table

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^RLG0.00%
Russell 1000 Value^RLV0.00%
Russell 2000^RUT0.00%
Russell 2000 Growth^RUO0.00%
Russell 2000 Value^RUJ0.00%
S&P 500^GSPC0.00%
Bloomberg Barclays AggAGG0.00%
13 Week Treasury Bill^IRX0.00%
Treasury Yield 5 Year^FVX0.00%
CBOE Interest Rate 10 Year T No^TNX0.00%
Treasury Yield 30 Year^TYX0.00%
Vanguard S&P 500 Index FundVOO30.00%
iShare Core S&P 500IVV0.00%
iShare Russell 1000 GrowthIWF10.00%
iShare Russell 1000 ValueIWD10.00%
iShare Russell 2000IWM5.00%
iShare Russell 2000 GrowthIWO5.00%
iShare Russell 2000 ValueIWN10.00%
iShare Russell MidcapIWR12.00%
iShare MSCI EmergingEEM0.00%
iShare Core MSCI EmergingIEMG8.00%
iShare MSCI EAFEEFA0.00%
iShare Core MSCI EAFEIEFA10.00%
BitCoinBTC0.00%
EthereumETH0.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]

)

)

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@shoof Use the IN operator instead of =


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@shoof Use the IN operator instead of =


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I actually typed that earlier and it underlines the IN OnlyTickersWithValues in red so I didn't run it because I thought it would give an error but when I run it it works!  Thanks for the suggestion to try again.  Do you have any idea why it would underline it like it's a syntax error but not give an error when running?  

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors