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.
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!
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @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 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!
You can try
FILTER(DimDeals,DimDeals[DealName]=LastNONBLANK(DimDeals_Sensies[DealName_Sensies],"")))
For further suggestion, please post some sample data and expected output.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
74 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |