Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |