Forum Discussion

Rich_Wyeth's avatar
Rich_Wyeth
Helper I
1 year ago
Solved

Using variables to return a result

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.
 
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      Rich_Wyeth
      Helper I
      1 year ago

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