How to use REPT DAX Function in Power BI / Real Life Scenarios // Beginners Guide to Power BI

in this video we're going to cover the wrapped Dax function in powerbi we're going to look at what it is how to use it and some scenarios on how you can use it in powerbi and in Excel all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solution SE the YouTube channel where we cover tips tricks and best practices when working with powerbi I upload new videos every week so make sure you hit that sub subcribe button and the bell icon to get notified when a new one is out so the rep Dax function is actually a fairly simple Dax function so you give it a text and you tell it how often it needs to repeat it and it will just return that whole string of text for you so this is the documentation for the rep Dax function so as I mentioned to you it just simply repeats the number or the text that you give it based on the number of instances that you you tell it in the parameters here so it takes the two values that we mentioned and there are a few remarks here like if the number of times is zero it just returns blank if it's not an integer in the number of times the value is tranced and there is a limits to the number of characters that it returns which is 32,000 characters here so if you're using or having to repeat characters up to that limits just be aware that you are going to hit some errors if you're using this function so let's have a look at just a quick example of how you might be able to or first let's have a look at how how it works right so let's uh let's start by creating a measure repeat and we're going to just use wrapped here so let's say you want me you want to just say this and then we want to repeat this three times if you put it in a let's say a card here as you can see it just repeats that text that you've given it three times and obviously as you change the number of times it changes the number of repetition that you can have so that's pretty simple there wasn't really a lot of explanation on how it works because it's super simple so now let's have a look at some different ways that you can use reps at least in a real life scenario so the first and the most easiest one that I have done in the past is by creating a sort of average or star ratings which involves five stars let's say if you have a three star rating out of five you would have three filled stars out of the five allar so you have three filled stars and two not filled stars and um one way that you can have or create this visual is by combining it with a few other functions but mainly using the Reps Dax function so this is the data set that we're looking at today which is a list of different movie titles and we have some different information here like the total number of votes what their ranks are and their average rating but what we're going to focus on is the average rating part which is um a decimal point average based on certain scores and what we want to do is to convert this into a festar rating and to do that we're going to use uh measure or Dax functions to to convert this and change this into Stars instead so what I'm going to do is I'm going to create a measure here so I'm just going to reuse this one we're just going to rename this into star rating and the first thing that we're going to do is we're going to convert the average rating into five scale so it's apparently or currently it's the average rating is between 1 to 10 but we want to change that to 1 to 5 so we're going to change average movie [Music] rating and uh well actually let's have a look and see what this gives us so it gives us the exact same value and then we're simply going to just divide this into two so the safe way to to divide as usual is by using the Divide XX function so now it gives us or we kind of round it up to a five digigit rating but we don't want the the decimals as well because we want to count the Stars instead of you know without any decimals because we want the full star for each point so we're going to wrap this with an integer with an INT function and that just forces and rounds those numbers up into single digit number rating so at the moment we're looking at spy but we can remove that filter so we just see everything so you can see now they are converted into three four five star ratings so now what we're going to do is we're going to wrap this in a variable because we're going to have to calculate and reference it later so instead of rewriting it we're going to just use a variable I'm going to name it stars and um we're going to create another variable called blank which is going to be basically the blank stars that we're going to fill in with the rest of the five star rating so we're going to Simply do five minus Stars which will give us the remaining number of stars that we need in that and then now we're going to just choose a return and we're simply going to combine and repeat the numbers uh based on the kind of repetition that we have created in the first two variables so the first thing is we're going to use rep and we're going to give this text so for now let's give it an X and let's say use the stars and then we're going to use the ENT to basically concatenate them and we're going to use Y and then blank so now what it's done is as you can see there's uh for every three star rating you will see that there's 3 x and then 2 y so that will just give us you know the number or the ratings and at the moment we're using text but we're going to replace that with a unit chart soon so so that you can see it a little bit easier but basically the X refers to the Stars the filled in stars and the Y's are the kind of the the outline Stars which is typically how you'd represent the five star rating so the rep basically chooses the number of stars and then the second rep just chooses the number of the outline Stars so okay I'm waffling on now but let's go and change these into actual Stars so to do that there are two options for you so the first thing is you can use the uh Emoji board which is you can access using the windows dots from your keyboard it will bring up this selection here so you can choose from emojis or from symbols if you can find it and if you know where they are however if you don't know where they are and you know the unichar codes of those those emojis you can use the function unichar as well to represent them so I know the codes um because I have used them before so if you wrap it with a uniart and the value is 9733 if you hit that enter you will see that's now the star the filled in Star and uh will'll replace this one also with a unitar 9734 I believe that's the one and there you go so as you can see that for each of these titles you have five star ratings the field in value of stars is based on a you know average that we have changed into kind of five stars or or five ratings and it gives you an average across all of the different titles that you have selected here so you might want to select just the Spider-Man movies and it will give you the five star rating across all of those different movies and you can have that as your own visual by itself in a card or something so the nextar that I found is actually a bit random but it's super simple that I thought I'd try to cover it and it's actually in Excel and it's a way for you to use rep to show Bars fairly easily without using any kind of pivot tables or anything like this in your data tables so we're going to do is we're going to go to my Excel file here which is basically just the list of our titles and the number of reviews for each of those titles I just created just a list a random list of reviews here that we can kind of use to represent some some values to to show and um you know the values by itself they are useful however you might want to represent this in a bar chart or you want to show them in something that is a little bit more you know that makes it a little bit more obvious which reviews have the highest volume for each of the titles so this is where you can use the rep trick so what we're going to do is we're going to use rep we're going to say use the pipe so we're going to use this one comma and then the number of times is based on the reviews that we have here in this table so we close this and here we go so it gives you a kind of a horizontal bar chart to show you the volume of reviews for each of these titles now if you want to make it look a little bit more like a bar chart you can change the type of font that you're using here so in this case for example the one that we can use is something called stencil which where is this so here we go so if you change it to stencil it just makes those characters um a little bit closer to each other and it gives you this sort of bar chart look and feel without actually creating any of those visuals in Excel so fairly simple and that's really it for this video I hope you now know how easy it is to use the wrapped Dax function in parbi thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so to do the better for next time ask your questions in the comment section box below so I can help you and you can help others if you like this video we have a patreon page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye-bye

Channel: Solutions Abroad

Views: 1,822

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, power bi rept, power bi repeat, power bi rept dax, power bi loop, power bi while loop, power bi stars

Id: Z2SQC9hyE64

Length: 10min 5sec (605 seconds)

Published: Wed Mar 06 2024