Multiple What If parameters for same table different rows

[thomazinh]

I want to use the What If parameter for multiple rows in a table and have different What If parameters for a specific Cost ID types. I'm trying to calculate the Hours/Bid Value or the selected Parameter for the corresponding Bid Factor. For example, for all WbsCodes containing a string of .AAA. I want to use the What If Parameter_AAA instead of the default Bid Value. There are mulitple Cost ID types but I am only needing it for three Cost ID types within the WbsCode. I tried using a SWITCH funciton, but I wasn't able to get it correct. Sample data and DAX are below. 

_Factored =
VAR AAABid =
    SELECTEDVALUE ( Parameter_AAA[Parameter_AAA] ) //Grabbing the selected what if parameter for AAA
VAR BBBBid =
    SELECTEDVALUE ( Parameter_BBB[Parameter_BBB] ) //Grabbing the selected what if parameter for BBB
VAR FFFBid =
    SELECTEDVALUE ( Parameter_FFF[Parameter_FFF] ) //Grabbing the selected what if parameter for FFF
VAR Hours =
    SUM ( Table4[Hours] ) //Sum of Hours
VAR Unfactored =
    DIVIDE ( Hours, SUM ( Table4[Bid Value] ), 0 ) // Unfactored Desired value
// Factored valued returns the selected Parameter for the WbsCode containing that string. For example, Parameter_AAA should only influence WbsCodes with that Trade type.
VAR Result =
    SWITCH (
        CONTAINS ( Table4, Table4[WbsCode], "" ),
        "AAA", DIVIDE ( Hours, AAABid, 0 ),
        "BBB", DIVIDE ( Hours, BBBBid, 0 ),
        "FFF", DIVIDE ( Hours, FFFBid, 0 ),
        Unfactored
    ) //   IF (
//       CONTAINS ( Table4, Table4[WbsCode], ".AAA." ) = ".AAA.",
//       DIVIDE ( Hours / AAABid, 0 ),
//       IF (
//           CONTAINS ( Table4, Table4[WbsCode], ".BBB." ),
//           DIVIDE ( Hours / BBBBid, 0 ),
//           DIVIDE ( Hours, FFFBid, 0 )
//       )
//   )
RETURN
    Result

Or trying this Result DAX 

VAR Result =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Table4'[WbsCode], ".AAA" ), DIVIDE ( Hours, AAABid, 0 ),
    CONTAINSSTRING ( 'Table4'[WbsCode], ".BBB." ), DIVIDE ( Hours, BBBid, 0 ),
    SEARCH ( ".FFF.", 'Table4'[WbsCode], 1, 0 ) = 1, DIVIDE ( Hours, FFFBid ),
    Unfactored
)

CostID WbsIndex Trade WbsCode Hours Cost BidValue

ALPH	1179	AAA	1179.AAA.10.ALPH	56	$  3,666.00	2.00
BRAV	1866	BBB	1866.BBB.10.BRAV	120	$  3,091.00	1.00
CHAR	1799	CCC	1799.CCC.10.CHAR	36	$  2,331.00	1.00
DELT	1010	DDD	1010.DDD.10.DELT	111	$  3,814.00	2.00
ECHO	1650	EEE	1650.EEE.10.ECHO	7	$  2,431.00	1.00
FOXT	1163	FFF	1163.FFF.10.FOXT	158	$  3,370.00	1.00
ALPH	1428	AAA	1428.AAA.10.ALPH	77	$  2,413.00	2.00
BRAV	1055	BBB	1055.BBB.10.BRAV	55	$  2,729.00	3.00
CHAR	1640	CCC	1640.CCC.10.CHAR	12	$  2,531.00	1.00
DELT	1977	DDD	1977.DDD.10.DELT	10	$  2,972.00	1.00
ECHO	1286	EEE	1286.EEE.10.ECHO	45	$  2,808.00	3.00
FOXT	1597	FFF	1597.FFF.10.FOXT	125	$  3,617.00	1.00
ALPH	1563	AAA	1563.AAA.10.ALPH	148	$  2,642.00	3.00
BRAV	1514	BBB	1514.BBB.10.BRAV	132	$  3,903.00	1.00
CHAR	1700	CCC	1700.CCC.10.CHAR	142	$  2,765.00	2.00
DELT	1491	DDD	1491.DDD.10.DELT	43	$  2,271.00	3.00
ECHO	1872	EEE	1872.EEE.10.ECHO	111	$  3,407.00	1.00
FOXT	1629	FFF	1629.FFF.10.FOXT	26	$  2,012.00	2.00
ALPH	1368	AAA	1368.AAA.10.ALPH	16	$  2,559.00	3.00
BRAV	1441	BBB	1441.BBB.10.BRAV	26	$  2,095.00	1.00
CHAR	1319	CCC	1319.CCC.10.CHAR	102	$  3,087.00	2.00
DELT	1750	DDD	1750.DDD.10.DELT	112	$  3,642.00	2.00
ECHO	1780	EEE	1780.EEE.10.ECHO	1	$  3,764.00	3.00
FOXT	1055	FFF	1055.FFF.10.FOXT	26	$  3,748.00	3.00
ALPH	1409	AAA	1409.AAA.10.ALPH	148	$  3,908.00	3.00
BRAV	1513	BBB	1513.BBB.10.BRAV	18	$  3,106.00	3.00
CHAR	1516	CCC	1516.CCC.10.CHAR	72	$  3,856.00	3.00
DELT	1236	DDD	1236.DDD.10.DELT	42	$  3,810.00	2.00
ECHO	1254	EEE	1254.EEE.10.ECHO	144	$  3,929.00	3.00
FOXT	1203	FFF	1203.FFF.10.FOXT	108	$  3,067.00	1.00

[johnt75]

Change your switch statement to

SWITCH( TRUE(),
    CONTAINSSTRING( Table4[WbsCode], "AAA"), DIVIDE( Hours, AAABid, 0),
    CONTAINSSTRING( Table4[WbsCode], "BBB"), DIVIDE( Hours, BBBBid, 0),
    CONTAINSSTRING( Table4[WbsCode], "FFF"), DIVIDE( Hours, FFFBid, 0),
    Unfactored
)

[thomazinh]

 

I'm returning an error "single value for column WbsCode in Table4 cannot be determined...column contains many values... without specifying... a min, max, count, or sum to get a single result." I don't want any of those, but I do need to search in the text string. I'm thinking I'll need to use a CONTAINSTRING or something to get around it? 

For more context, here is the link to the PBI file. 

[johnt75 · Accepted solution]

I thought it was a calculated column, not a measure. Try wrapping the Table4[WbsCode] in SELECTEDVALUE

[thomazinh]

I'm able to get around the error, but it doesn't look like its calculating what I'm needing now. 

Keeping all parameters at 1,

vs changing the parameters to various numbers.

EDIT**** The individual Factored values changed, but the Total at the bottom of the table did not change. The proposed solution worked. Is there a reason the Total at the bottom didn't update?