Forum Discussion
Multiple What If parameters for same table different rows
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 |
I thought it was a calculated column, not a measure. Try wrapping the Table4[WbsCode] in SELECTEDVALUE
4 Replies
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 )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.
I thought it was a calculated column, not a measure. Try wrapping the Table4[WbsCode] in SELECTEDVALUE
