Problem with filter context

[MagnusRosenPBI]

Hi

I am having some issues with understanding filter contexts, specifically with a date dimension. Hopefully someone smarter than myself can point me in the right direction.

Please see this pbix-file -> File

Bonus theme file -> Theme

In the file I have a data model designed to visualize time weighted returns for a chosen portfolio in a chosen currency and over a specified date period, thus 3 dimensions of choice for the report viewer (thus 3 slicers). I calculate the indexed return for each portfolio in each currency before visualization and the time series is stored in the CachedPerformance[Index].

What I am trying to accomplish is letting the index series always start at 100 when the viewer changes the date slicer starting point. To accomplish this I create some measures:

* [TWR - Index] -> The thought here is to simply "pull up" the column value from table CachedPerformance and rebase it to the currently filtered date context.

* [TWR - Rebase Factor] -> Used by [TWR - Index] to calculate a rebased index value.

My question:
The pbix file is set to have 2017-04-26 (there is date up until 2017-04-27) as the ending date for the date slicer on report page "Single Portfolio - Performance". The reason I need to do this is because otherwise I wont have a defined filter context for the date dimension and my measure [TWR - Rebase Factor] fails to function, or rather, there is no filter applied and thus ALLSELECTED(TimeLine[Date]) becomes the full timeline and my rebase factor becomes blank which in turn, makes the [TWR Index] fail.

Right now the central part of [TWR - Rebase Factor] is:

CALCULATE(SUM(Cachedperformance[Index]); FIRSTDATE( ALLSELECTED(TimeLine[Date])));

I would like it to look something like this:

CALCULATE(SUM(Cachedperformance[Index]); MAX( FIRSTDATE( ALLSELECTED(TimeLine[Date])); Portfolio[PortfolioStartDate] );

The problem is that I cannot understand how to make that happen. Portfolio[PortfolioStartDate] is a column in the Portfolio table and thus a defined value for each portfolio and should be filtered by my choice of portfolio in the slicer for that dimension.

I have tried different ways, such as:

CALCULATE(SUM(Cachedperformance[Index]); MAX( FIRSTDATE( ALLSELECTED(TimeLine[Date]));LASTNONBLANK(Portfolio[PortfolioStartDate];Portfolio[PortfolioStartDate] ) ));

This fails to function because of "FIRSTDATE being used in a TRUE/FALSE expression that is used as a table filter expression". I don't really understand why this is unallowed. I was allowed to use FIRSTDATE before I introduced the max function in the expression. Clearly I don't fully understand how to treat filters and contexts and it is driving me mad. Please educate me on what I am doing wrong here.

[v-yulgu-msft · Accepted solution]

Hi @MagnusRosenPBI,

Because FIRSTDATE function returns a table containing a single column and single row with a date value, we cannot directly use FIRSTDATE nested in MAX. That is why the error prompts "FIRSTDATE being used in a TRUE/FALSE expression that is used as a table filter expression". In your scenario, please replace MAX with MAXX whose syntax is like MAXX(Table,expression).

Best regards,
Yuliana Gu

[MagnusRosenPBI]

Hi Yuliana

I hear what you are saying but there is still something I am missing.

If I write:

CALCULATE(
SUMX(PortfolioPerformance;PortfolioPerformance[Index]);
MAXX( FIRSTDATE( ALLSELECTED(TimeLine[Date]) ); "expression"  )
)

What do i put instead of "expression". I know how to treat the MAXX function when I am deailing with a defined table (expression evaluated for each row) but here i am getting a table returned from FIRSTDATE, how do reference this anonymous table?

Also, I want MAXX to find the max value of two dates:

• FIRSTDATE( ALLSELECTED(TimeLine[Date]) ) -- one single date
• Date(2016;9;30) -- static date that might be retrieved from another table

Basically I want to calculate SUMX(PortfolioPerformance;PortfolioPerformance[Index]) for a specific date. This date should be the largest of the first selected date and another static date.