Using variables to return a result

[Rich_Wyeth]

Hi all,

I am trying, for the first time to use variables, but I have come a little unstuck and I know the below is not correct, but hopefully it will help to understand what I am doing.

I have a table that is just a list of Part Numbers with results under columns for each month, QTR and HY. I am looking to get the current QTR and then based on that and the Part Number that has been filtered, to select the appropriate number from the correct QTR column.

i.e.

Part Number QTR1 QTR2 QTR3 QTR4

1234567        100    200   300    400

So if current QTR is 1 and Part Number 1234567 has been selected on the filter then return column QTR1 and so on.

What I have so far is:

M_CURRENTQTR_Value =

VAR vPart =

    SELECTEDVALUE('QTY SOLD DATA TABLE'[Part Number])

VAR vQTRCurrent = [M_CURRENTQTR]

VAR vQTR1 =

    SELECTCOLUMNS('QTY SOLD DATA TABLE',[QTR 1 No Max])

VAR vQTR2 =

    SELECTCOLUMNS('QTY SOLD DATA TABLE',[QTR 2 No Max])

VAR vQTR3 =

    SELECTCOLUMNS('QTY SOLD DATA TABLE',[QTR 3 No Max])

VAR vQTR4 =

    SELECTCOLUMNS('QTY SOLD DATA TABLE',[QTR 4 No Max])

VAR vTable =

    FILTER('QTY SOLD DATA TABLE',[Part Number]=vPart)

VAR vResult = IF(vQTRCurrent=1,vQTR1,IF(vQTRCurrent=2,vQTR2,IF(vQTRCurrent=3,vQTR3,IF(vQTRCurrent=4,vQTR4,0))))

I know this is wrong and I get an error The syntax for ')' is incorrect.

I also know I am not using the vTable anywhere, but I can't figure it out. Any help or any other solution would be greatly appreciated. Just trying to learn as I go.

[bhanu_gautam · Accepted solution]

@Rich_Wyeth , Try using

DAX
M_CURRENTQTR_Value =
VAR vPart = SELECTEDVALUE('QTY SOLD DATA TABLE'[Part Number])
VAR vQTRCurrent = [M_CURRENTQTR]

VAR vTable = FILTER('QTY SOLD DATA TABLE', [Part Number] = vPart)

VAR vResult =
SWITCH(
TRUE(),
vQTRCurrent = 1, MAXX(vTable, 'QTY SOLD DATA TABLE'[QTR1]),
vQTRCurrent = 2, MAXX(vTable, 'QTY SOLD DATA TABLE'[QTR2]),
vQTRCurrent = 3, MAXX(vTable, 'QTY SOLD DATA TABLE'[QTR3]),
vQTRCurrent = 4, MAXX(vTable, 'QTY SOLD DATA TABLE'[QTR4]),
0
)

RETURN vResult

[Rich_Wyeth]

Thank you so much, works a treat. I can see where I was going wrong, all learning in progress.