Forum Discussion

LaurenL's avatar
LaurenL
New Member
8 years ago
Solved

DAX function to Excel "Choose"

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.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

     

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      LaurenL
      New Member
      8 years ago

      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's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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