DAX function to Excel "Choose"

[LaurenL]

Hi,

Hoping someone might have run into a similar issue before.

I'm using the below Excel formula to populate cell rows with the "Choose" function. 

IF((GM$9-$AW10)>0,CHOOSE(GM$9-$AW10,$AY10,$AZ10,$BA10,$BB10,$BC10,$BD10,$BE10),"")

When nested with other similar IF statements, this allows me to copy several a sequence of rows next to each other.

I am looking to migrate to PowerPivot but cannot find similar function in DAX?

Anyone have any ideas?

Thanks in advance!

Lauren.

[MFelix]

Hi @LaurenL,

Without any additional information it's not easy to get an answer to you but you can try and use the SWITCH function look at the explanation on this and can also google it and find several examples.

Regards,

MFelix

[LaurenL]

Hi @MFelix

Thank you for your reply. I have tried to use the SWITCH funtion. but the formula I need to build is quite long. 

Here is the full formula I currently use in Excel:

=IFERROR(IF(GM$9>$BS10,"",IF($AW10=GM$9,$AX10,IF((GM$9-$AW10)<($AV10),IF((GM$9-$AW10)>0,CHOOSE(GM$9-$AW10,$AY10,$AZ10,$BA10,$BB10,$BC10,$BD10,$BE10),""),IF($BT10=GM$9,$BU10,IF((GM$9-$BT10)<12,IF((GM$9-$BT10)>0,CHOOSE(GM$9-$BT10,$BV10,$BW10,$BX10,$BY10,$BZ10,$CA10,$CB10,$CC10,$CD10,$CE10,$CF10,$CG10,$CH10,$CI10,$CJ10,$CK10,),""),
IF($CL10=GM$9,$CM10,IF((GM$9-$CL10)<52,IF((GM$9-$BR10)>0,CHOOSE(GM$9-$CL10,$CN10,$CO10,$CP10,$CQ10,$CR10,$CS10,$CT10,$CU10,$CV10,$CW10,$CX10,$CY10,$CZ10,$DA10,$DB10,$DC10,$DD10,$DE10,$DF10,$DG10,$DH10,$DI10,$DJ10,$DK10,$DL10,$DM10,$DN10,$DO10,$DP10,$DQ10,$DR10,$DS10,$DT10,$DU10,$DV10,$DW10,$DX10,$DY10,$DZ10,$EA10,$EB10,$EC10,$ED10,$EE10,$EF10,$EG10,$EH10,$EI10,$EJ10,$EK10,$EL10),""),""))))))),"")

It takes weekly data from 3 sections of columns (1st section can have from 1-8 points of data (columns), 2nd section can be from 1-17 points of data, 3rd section is 1-52) and "summarize" those data points into a continuous weekly calendar. 

Thanks,

Lauren

[v-huizhn-msft · Accepted solution]

Hi @LaurenL,

Even through you post the full formula in excel, it's still hard to reproduce your scenario, you'd better create sample table and list expected result. For Choose function in excel, you can use SWITCH function in DAX.

For example, I use the Choose funtion in excel as follows.



In Power BI desktop, you can use SWITCH function in DAX to create a calculated column as follows.

Output = SWITCH(Test[Input],1,"red",2,"blue",3,"green")

For if function, you can still use it in DAX, more details, please review this blog: https://msdn.microsoft.com/en-us/library/ee634824.aspx.

Best Regards,
Angelia