Forum Discussion
DAX function to Excel "Choose"
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
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
Microsoft EmployeeHi 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