DAX Fridays! #42: SUBSTITUTE (multiple values)

[@jamfero]

I have spent hours and I mean hours making a spreadsheet for a game and I have been using drop down menus for the maths of stuff I need to make in bulk but only x item would show as that was the original substitute. This video has been the only 1 out of about 30 or so videos to actually fix my spreadsheet bug. Thank you so much!

[@CurbalEN]

At your service 🤩

[@VeganSmasher]

Thank you for this excellent demo! I used what you showed to create a substitution cypher to 'scramble' and 'descramble' employee names or other confidential info. What a great command! Thanks again!

[@CurbalEN]

Cool 👏

[@CarlotaFigueroa-n1b]

Ahhhh i spent half an hour trying to solve this and you did it for me. Thaaaanks

[@CurbalEN]

🥳🥳

[@bradj229]

Cool function! Thanks Ruth!

[@CurbalEN]

Keep learning 👏👏

[@TheDistractionStudio]

Another useful episode of DAX! I always look forward to your videos. Thanks

[@CurbalEN]

+rajan77 Thanks Rajan, mean a lot! Have a great weekend :) /Ruth

[@123jamalq]

Great Work Curbal, I learnt a lot from your tutorial.

[@CurbalEN]

+Jamal Qamar Fantastic !!! Thanks Jamal! :) /Ruth

[@excelisfun]

Thanks for the SUBSTITUTE video : )

[@CurbalEN]

+ExcelIsFun You welcome! My pleasure 😇 /Ruth

[@erickammerer7821]

This was very helpful and clearly explained. Thank you!

[@CurbalEN]

Happy to hear! /Ruth

[@SmartBimson]

Niiice video Ruth !!! And today I needed this function at work.. now I know for next time 👍🏻💪🏻 have a great weekend 🙋🏼‍♂️☀️☀️☀️

[@CurbalEN]

+SmartBimson Really? I am a mind reader! :) /Ruth

[@SmartBimson]

Curbal Ojalá no puedes leer mi mente 😂😂😂

[@CurbalEN]

+SmartBimson Y eso? Está demostrado que puedo 😂 /Ruth

[@SmartBimson]

Curbal bueno vamos a ver Qual dax expresión explicas en la próxima semana ... si está en el contexto del Tópico la semana pasada, esta DAX será increíble 😂😂😂😂😂😂

[@CurbalEN]

+SmartBimson Trato hecho! 😂 Como lo adivine... cambio de profesión 😂😂😂 /Ruth

[@gregwilliams7848]

Thanks! This helps a lot!

[@CurbalEN]

Yey!! Great! :) /Ruth

[@naziankumat4596]

Thanks a lot!

[@lisascape]

You say that this should be done in Power Query, but I can't find any way to do that there. Maybe it's because this is an old video, but it is so frustrating because my dataset is too large to do this in the Data view. Everything I'm reading says I should be using M code. Can Substitute no longer be used in Power Query??

[@CloudhoundCoUk]

Great option.

[@CurbalEN]

+Cloud Hound Thanks Christopher, and many thanks for all the shares in twitter! /Ruth

[@djeritorres]

Hola Ruth. Excelente clase. Una consulta, ¿Harás un resumen de toda la charla que ha hecho Microsoft sobre Power BI?. En los siguientes meses se vienen novedades. Saludos

[@CurbalEN]

+djeritorres ah si! Buena idea! :) La semana que viene! /Ruth

[@djeritorres]

Curbal Estaré a la espera entonces. Recién estoy en el tercer vídeo de los tantos que han subido en estos días. Gracias

[@CurbalEN]

+djeritorres Ya me imagino, muchas novedades :) Intentaré sacarlo tan pronto como pueda! /Ruth

[@djeritorres]

Curbal Gracias Ruth. Saludos...

[@CurbalEN]

+djeritorres Muchas gracias a ti por tu apoyo y pasa un buen fin de semana! /Ruth

[@joseluisk18]

Thank you

[@stevennye5075]

very useful

[@CurbalEN]

Awesome 😊 /Ruth

[@PS65501]

How to replace multiple spaces in between a string with single space ?

[@cesarebernardo7315]

Hi! Let's say in the description column of the tutorial, you had acronyms instead of full names, for example, "BC-10" instead of "cheeses" and "SW-05" instead of "seaweed and fish" how could you combine the MID and the substitute formula? What i mean is extract the "BC" from "BC-10" and substitute "BC" with "blue cheese" and do the same for "SW-05" meaning extract the "SW" and substitute it with "seaweed". I have a document full of codes, need to extract the first letters of the code and automatically need a formula to tell me what that acronym stands for, in my case it would be the category of the product. Hopefully i've been clear enough. I would be greatly thankful if somehow you could help me out. Thank you very much. Cordially, Cesare Bernardo

[@nsr6]

Power BI nesting of functions is little annoying some times, when we have to do multiple times. Is it not better to have a chaining method be a better way like in python pandas. Just a thought.. May be they should have designed it in a object oriented way for power BI atleast.

[@CurbalEN]

Mmmm ni idea how python panda works so can’t really contribute with any meaningful comment here, but The Power bi team is always looking for feedback to improve the product. Make sure you let them know here: Ideas.powerbi.com /Ruth

[@mathijs9365]

I dont understand the logic of multiple substitute.If you have the second substitute . Where is the reference to the text or column? Because the first substitute is an outcome.

[@CurbalEN]

+Mathijs Because you are changing text on the same column, when you nest the substitute function, you only need to reference once. /Ruth