Using DAX to format a list of values in Power BI Desktop

Patrick yeah this one's gonna be hard it's gonna be tough see me yeah yeah take it to the next level we gotta do something next level yeah I know who we need I know who we need we need Marco I'm making a wish I don't know we don't need Marco we need Alberto we need to go to next we need to go to the next level Marco can't handle this next up we need Alberto right so I'm gonna make a wish I'm wishing I'm wishing yo what's up this is Patrick Alberta and in this video we're going to talk about displaying a list of values using Dax and power bi stay tuned all right Alberto so my wish came true my wish came true you're here I did a video on using that concatenated lists the values quick measure and somebody made a comment about okay Patrick so what I did was I had a slicer and I select one year I select another year it's like another year and I just displayed them in the list but they say it's all the body of all the values right and I modified it a little bit to accommodate something but they wanted to instead of late displaying all the values display a range oh you follow me and so I immediately posted answers like all right some dacks for you cuz I'm good right - you got this class and I'm good but then as soon as I posted a question I go I betcha there's a blog post or an article or something and somebody who already did it so you open up your favorite web browser choice I use Bing right and I did a quick search and bam I landed on your article and I start reading through it and I go I think I can explain this but I wish Alberto was here to explain this for me you think you could do that for me oh yeah all right let's get to Lee you know how the article was born no tell me I just woke up in the morgue anga and what's the first thing they do when you wake up I drink a cup of coffee so the article was born the article was born I mean was early morning and I woke up upon this interesting Western and I started working on it and making a tea and typically I wake up my kids I prefer than breakfast and stuff I didn't do anything that morning I was a thinking fee on it was so the problem yeah so you're gonna show me this right come with me so instead of all this talking you guys know what I like to do all right let's head over your laptop so you see I have this a power bi file and then you have the monster selected here and I have a label here that shows of the selected month gotcha it was a life may just like a June on the label changes here and if you like different values like a March and May that works your March I made the cool thing is that you can select you can select March May June July it shows March and then make July what if I suggest select the loop December December what if I skip all way to December also have multiple ranges and it just warms oh I like it I like it I like it can you show me how this works absolutely it's a piece of tags now just focus on the list of Monza because we are going to start from here when we look at the code okay but we have a list of months some of them are selected some of them are not okay so would ask this piece of tax do the survey turn Monza month numbers is just a variable that contains the values of the month number K we are not taking the month name so we take it amount number sir oh so not strings now stream okay the slicer contains the strings I don't mind about the streets for now I lied about the now guys I want to solve them the right way yep yep yep and I have another variable months and names that contains both month and name so it contains the number and of the name card chip we will use the two variables for two different markets okay now the thing is in order to generate this list you need to understand the scanning phase list of Monza whether you are not the beginning of a series at the end of a series or in the middle of it because that's the hard part in the middle in the middle yes it's not that hard okay Koza concatenate X is just an iterator so it is a scanning the month and names variable gosh we have both month and names in the same variable so we have the number in the end this ain't a gotcha from here we take the mountain lumber and we store it in current month number and current month name is the current monthly just two verses yes yep then in order to understand whether you are at the beginning in the middle or at the end you simply check if the next month is in the selected map and now remember selected months only contains the selections yes when you are in March you check is April selected yes if every is not selected I am at the end of a serious got chipped okay I gotcha I gotcha preview selector tell me if the previous month is selected so I was in every a check for March of tip was March sled might be yes or no yep now the trick is while you're scanning month by Martha when you are at the beginning you just output your name the man's name yep if you are at the end you output a comma because of what comes next that is the next series yes if you are not a beginning but not at the end then you output - right so if I if I have May in the list and we are on main and then I have October in the list it's smart enough to say hey you're at the end on May put a comma and go to the next month no that's okay alright it just can look at the things listed okay the list will contain March May June July yep so it goes on March oh yeah and say well I am in March of is the previous elected no no is the next selected know where I goes it's opera yep okay therefore it just outputs the name gotcha is followed by a comma gotcha because the next is not selected when it will be May it just outputs the name mm-hmm followed by what because the next is selected is followed by a - then it will find June June is in the middle both the previous and the next are sites while you have the previous analysis June will be skipped yep got it then it goes on July here the previous is selected the next is not selected so it outputs a name followed by a comma and all the rest is finished okay got it at the end of this whole process this is happening here if the previous is selected and the next is selected if they are not both select an antenna tapas the current name plus a dash or a comma depending if it's the first or the last our selection my man is blown but it's so cool yeah at the end of all this process what happens is that you have a last comma let remainder because it's not more than I have to understand that the last that doesn't have a following at section in that sense is why that's the left let's say hey out of the result get rid of result - - and let's get rid of the common space exactly oh and you can easily see if you instead of returning everything you just returned resolved you you see that there is a trailing comma at the end that need to be assumed that can you can you take it out absolutely that's why we use variable so yeah we can debug it we can debug it with variables absolutely then return resulta and then we get rid of the max code and you see that we have that need to be removed so the engine the engine the formula said we've had March and March was enough then we had my that was the first so it have output it a - then it skipped the month in between any luck with July it skipped the mutton between because there was one before and one another yes again we're in between your police not different but yeah that is pretty nice it goes that way you can produce the list of values in a human readable way but it's critical that we use numbers and not try to use strings right if we want it to be in a way in a proper order exactly yeah and this is the reason why we have two variables so the month and name set contains both the month and the names and we use it because we wanted to get the names but when we want to check if the previous or the next is selected we use the in operator in requires a table culture checks a number that is present in a table the table need to only contains numbers it cannot contain numbers and strings otherwise it will not find them and we were let's move the numbers make it easy so we can go forward and go back exactly yeah guys why we got it so man this is great so now I understand and able to write this but I'm just gonna copy and paste it thank you so much Alberto if you guys have questions comments anything right you know what to do post it in the comments below this your first time visiting the guy in the cube Channel you know what to do hit that subscribe button alright hey my video and doctor you got to give me two big thumbs up as always from Adam in myself and I'm Tom thanks for watching and we'll see you in the next video

Channel: Guy in a Cube

Views: 29,984

Keywords: dax, alberto ferrari dax, power bi dax, power bi dax advanced, power bi dax measures, power bi dax tutorial, power bi dax variables, alberto ferrari, alberto ferrari power bi, power bi desktop, power bi desktop tutorial, sqlbi, sqlbi power bi

Id: QYbl4k_CWBE

Length: 9min 4sec (544 seconds)

Published: Wed Mar 13 2019