Solved: VALUES() equivalent for multiple selection in a sl... - Page 2

gLB · ‎07-31-2017

Hi,

I am currently working on a portfolio analysis model where I need to do a specific operation on multiple selected deals.

My goal here is to be able to run the CALCULATE() on more than one selection. Currently I can do it with one selection in my slicer using the VALUES() formula. It doesn't look like VALUES() can handle more than one selection because I keep getting the error: " A table of multiple values was supplied where value was expected"

In the filter of my CALCULATE I want the DimDeals table to find the selected deals and then do the calculation only for those selected deals. What should I do to be able to CALCULATE on more than one selection? Can it be achieved in Power BI?

See below a quick example of what I am trying to do:

Sensies_MultipleDEALS =

CALCULATE(SUM([NCF]),
DimDeals[DealName]=VALUES(DimDeals_Sensies[DealName_Sensies]))

where DimDeals_Sensies is a table with all the name of the deals. I want to be able to select more than one deal.

The "real" formula is longer than that. The other deals that are not selected with have their own calculation.

DimDeals_Sensies slicer

Deal 1 Selected

Deal 2

Deal 3

Deal 4 Selected

Deal 5

By selecting the Deal 1 and the Deal 4 my measure should return the sum of the NCF only for those deals.

Thank you!

v-shex-msft · ‎08-10-2017

Hi @gLB,

I modify your formula, perhaps you can try it if suitable for your requirement.

Calculate column:

DealName = LOOKUPVALUE(DimDeals[DealName],[Deal_Index],[Deal_Index])

Measures:

Date_Loss_Select = IF(HASONEVALUE(Date_Loss[Date]),VALUES(Date_Loss[Date]),BLANK()) 

Loss_Select = IF(HASONEVALUE(Loss[Loss]),VALUES(Loss[Loss]),BLANK()) 

Before Loss Date = 
CALCULATE(SUM(CashFlows[CashFlow]),FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]<=[Date_Loss_Select]))

After Loss Date = 
CALCULATE(SUM(CashFlows[CashFlow])*(1-[Loss_Select]),
FILTER(ALLSELECTED(CashFlows),CONTAINS(ALLSELECTED(DimDeals),DimDeals[Deal_Index],CashFlows[Deal_Index])),
FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]>[Date_Loss_Select]))

Except Selected Loss = 
CALCULATE(SUM(CashFlows[CashFlow]),
FILTER(ALL(CashFlows),NOT(CONTAINS(DimDeals_Loss,DimDeals_Loss[Index_Loss],CashFlows[Deal_Index]))),
FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]>[Date_Loss_Select]))

Portfolio_Deals_Loss_Analysis = 
[Before Loss Date]+[After Loss Date]+[Except Selected Loss]

Result:

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post