Create calculated column from a measure (using SELECTEDVALUE) and existing column data

[mrothschild]

Link to PBIX file: https://drive.google.com/open?id=1LKAvDaMCVBLLkhMiEU8wrsKpnOBLFVWD

I'm trying to create a calculated column for the table shown below which is equal to the product of column [B] and column [C].

[C] is the SELECTEDVALUE of a slicer.

The code I've tried, which is returning blank: 

Portfolio Contribution - S&P 500 = 
PRODUCTX('Returns - Cambridge and Indices',
    SUM('Returns - Cambridge and Indices'[S&P 500 Index]) * [Weight - Equities - Public - S&P500]
)

I've also tried even simpler:

Portfolio Contribution - S&P 500 = 
    SUM('Returns - Cambridge and Indices'[S&P 500 Index]) * [Weight - Equities - Public - S&P500]

But keep coming up with BLANK() column data. 

Separately, if you're in the PBIX file and have an idea on how I can limit the "Less Than" slicer titled "Public Equities" to a maximum value of the SELECTEDVALUE from its parent slicer titled "Total Equities", I'd be extremely grateful!

Thanks in advance!!

[v-yangliu-msft]

Hi  @mrothschild ,

This seems to be because of this error message. The calculation table cannot generate data due to insufficient memory. The column data used in selectvalue() is empty, which results in Blank().

After that, you can change its calculation column to measure to respond to the slicer:

Measure =
PRODUCTX('Returns - Cambridge and Indices',
    SUM('Returns - Cambridge and Indices'[S&P 500 Index]) * [Weight - Equities - Public - S&P500]
)

This is the related document, you can view this content:

https://www.reddit.com/r/PowerBI/comments/gqkeq1/memory_error_how_to_deal_with_it/

https://maqsoftware.com/expertise/powerbi/dax-best-practices

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=6cd37af6-...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

You are defining this as a calculated column rather than a measure. Your second version would probably work as a measure.

A calculated column cannot be responsive to dynamic slicers since it's only calculated when the data model is loaded or refreshed and not in response to filters and slicers on the report pages.