Forum Discussion
VALUES() equivalent for multiple selection in a slicer
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!
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
13 Replies
Hi gLB,
You can try to use the source table of slicer as the filter on the measure.
For example:
Sensies_MultipleDEALS = CALCULATE(SUM([NCF]),FILTER(ALL(DimDeals),CONTAINS(ALLSELECTED(DimDeals_Sensies),DimDeals_Sensies[DealName_Sensies],DimDeals[DealName])))
Regards,
Xiaoxin Sheng
Hey Xiaoxin,
Thanks for the swift reply.
It looks like the CONTAINS is good to identify more than one selection in a slicer but there is a probem.
Instead of displaying the sum of each selected deals individually, it is displaying the sum of both deals on every deals. Example below:
Deal 1 - Selected sum of cash flows is 2,000
Deal 2 - Not Selected
Deal 3 - Not Selected
Deal 4 - Selected sum of cash flows is 3,000
Deal 5 - Not Selected
What is currently displayed using your formula:
Deal 1 - 5,000
Deal 2 - 5,000
Deal 3 - 5,000
Deal 4 - 5,000
Deal 5 - 5,000
What I would like to get:
Deal 1 - 2,000
Deal 2 - 0
Deal 3 - 0
Deal 4 - 3,000
Deal 5 - 0
I really need the non-selected deals to be displayed because my next step is to do a different calculation on the non-selected deals vs selected deals.
Please let me know if you need more info to help me on this matter.
Thank you for your help!
Hi gLB
Is it possible to share the data and the output expected. Please load the dat and requirement to one drive and share the link.
Cheers
CheenuSing
