DAX - CALCULATE only for selection in slicers using VALUES()

[gLB]

Hey guys,

I am working on a portfolio sensibilities model and I am having a hard time creating a formula to stress more than one portfolio at a time.

My goal is to be able to stress multiple portfolios by multiplying the future cash flows after a selected date by a selected loss percentage. 

My calculate formula is linked to 3 slicers:

1) Deals I want to stress (works with one selection, not many...)

2) Date of the data I want to stress (greater than this date)

3) How I want to stress the data (example: 25% cash flow future loss after the selected date (2))

With my current formula I am able to only select one deal to stress. If I want to stress more than one I get this error message:

"A table of multiple values was supplied where a single value was expected."

See below my current formula:

CALCULATE(SUMX(Cashflows,Cashflows[NetCashFlows]*(1-VALUES(Loss[Loss]))),FILTER(Cashflows,Cashflows[yearmonth]>VALUES(DimDate_Sensies[Month-Year_Sensies])),FILTER(DimDeals,DimDeals[DealName]=VALUES(DimDeals_Sensies[DealName_Sensies])))

Cashflows[NetCashFlows] = the monthly cash flows of every portfolios

Loss[Loss] = a slicer with 25%, 50%, 75%, 100%. Single select 

DimDate_Sensies[Month-Year_Sensies] = a slicer with a set of end of month date. Single select. 

DimDeals_Sensies[DealName_Sensies] = a slicer with all the portfolio names. Multiple selection.

I think the error is in that part of the code, the other lines are pretty simple and always worked in my other models:

FILTER(DimDeals,DimDeals[DealName]=VALUES(DimDeals_Sensies[DealName_Sensies])))

The goal of this line is to find every deals selected in the slicer, find them in the dimensions and do the calculate only on those selected deals. Unfortunately the formula I am currently using is not supporting more than 1 selection. Is it because the VALUES works this way? Is there aynyway I can do model this in Power BI?

Thanks for your help!

[MFelix]

Hi @gLB,

Not on the computer rigth now but try ALLSELECTED instead of VALUES

Regards,
MFelix

[gLB]

Hey @MFelix,

Thanks for your quick reply!

I've tried switching the VALUES() for ALLSELECTED() and I'm getting the same error: AMdxScript(Model) (90,203) Calculation error in measure ... : A table of multiple values was supplied where a single value was expected.

FILTER(DimDeals,DimDeals[DealName]=ALLSELECTED(DimDeals_Sensies[DealName_Sensies])))

Thanks!

[Eric_Zhang]

---

@gLB wrote:

Hey @MFelix,

Thanks for your quick reply!

I've tried switching the VALUES() for ALLSELECTED() and I'm getting the same error: AMdxScript(Model) (90,203) Calculation error in measure ... : A table of multiple values was supplied where a single value was expected.

FILTER(DimDeals,DimDeals[DealName]=ALLSELECTED(DimDeals_Sensies[DealName_Sensies])))

Thanks!

---

@gLB

You can try

FILTER(DimDeals,DimDeals[DealName]=LastNONBLANK(DimDeals_Sensies[DealName_Sensies],"")))

For further suggestion, please post some sample data and expected output.