Unlocking SELECTEDVALUE in Power BI: How to Return Filtered Tables and Overcome Scalar Errors #dax

hi there recently one of our subscriber messaged us that he wanted to create a table that is based on the selected value text function and also he would like to change something in the table based on the dynamic calculations or let's say by selecting the slicers but it was not possible and he was getting a table but still there was no data was that the same case with me or did I find something else well if you would like to know more please stay tuned with me till the end of this video and if you are over here for the very first time please don't forget to subscribe this Channel and hit the Bell icon so that you are up to date without all the videos and updates thank you my name is Ajay Kumar and I create videos on Microsoft power B and Microsoft Azure and in this video we are going to discuss about the selective value tax function and other functions that we can use with it so without wasting any more time let's get started all right now let's start with the functions that we are going to use in this video tutorial in this video first we are going to use the sum X sumex is a DX function that is going to return the sum of an expression evaluated for each row in a table this is similar to some function but in the sum function you cannot use an expression here you can use the syntax is going to be pretty simple where you have to use the sum X which is your text function then your table and then the expression over here after that we are going to use the selectric valued X function this function is going to return you the value when the context for column name has been filtered down to one distinct value only you cannot Supply more than one value at a time please do remember that otherwise it's going to return you the alternate result syntax is also again pretty simple first you have to write the selected value then your column name and the alternative result but you would like to return over there now coming to the last function which would be your filter X function filtered X function is basically going to filter the data that you are looking for it returns a table that represents a subset of another table or an expression so in this text function you have to first write your table name and then the filter that you would like to apply so that was the scenario where user is saying that I want to use the selected value and then also I want to return a table so what was happening let me take you over first my data model I'm using Adventure works 2012 data over here and this is my very pretty simple data model this is based on Star schema where I have fact internet sales table at the center and then I have given customer dim product and dim date table all around it now what I'm gonna do I'm going to create tables using selected valued X function and how we can do that well I'm gonna come over here to my data page over here you will find this new table so click on this one so what I'm gonna write over here I'm going to write a very simple Dax expression over here where I'm going to create a table based on my gender selection and there I'm gonna use my selected x value function so this is my pretty simple expression over here and you will see it's showing me one error but it's because I haven't put a comma over here now this is clear so I have a ding customer table where I have a gender column so what I'm saying that filter this and create a table where I can select the gender because I'm using over here this selected value text function which is going to allow me to select a value so whatever value I'm going to select for the gender filter or slicer it's going to return a table based from that so I'm using selected value n by gender name over here so let's select this you will find that table has been created and let me close it so now you can see on my right hand side at the bottom this gender based data table has been created and you will see all the column names and everything is over here but now let's check whether really my data is there or not so let me just close this and you would find that my table has been selected but there's no data at all so the one reason can be I haven't make my selection over there so what I can do I'll come over here I'm going to create a new page here let me select some of the fields quickly over there it's just for the demonstration purpose so please don't go into much detail over here I'm just clicking anyhow and since my selected valid X function is taking gender from the dim customer table over here is my agenda so I'm gonna create a slicer out of it so let me create a slicer this is pretty easy so this is my slicer over here but if I'm even I'm selecting male or female none of the data is appearing that means this table is not getting any of the data however irrespective of that if I'm gonna change my code over there let's say if I'm over here and rather than selecting this selected value if I'm gonna select only mail so let's see whether it can populate some data or not and now it's saying that my text expression is strong but why it's wrong so let's check again into the code and over here it's because of this so let me just delete this and here you can see that all the general related data is appearing over here and even I'm gonna fetch this gender over here you will find this General related data is there so no matter what you select over here so you will find that data is pretty coming over here and everything is working fine and also since we have selected only mail in the table expression so that's why all the mail is coming over here irrespective what I select over here so that is not gonna work over here now the real question is why the selected value tax function is not working over there is a reason for that when you use the selected value tax function it doesn't work in this way and please do let me know in the comment section as well what are your thoughts over it why it's not working but we can make it work and how we can make it work we have to use it with other Dex functions so now what I'm gonna do I'm going to create sales based on my Dax function how I can do that so now what I'm going to do I'm gonna use another text function that is some X text function and there I'm gonna calculate the total sales for males and females and my value is going to change dynamically through this slicer so let me just go over there and what I'm going to do I'm going to create a new measure so let me close this in the fact table first I'm going to create a total sales measure total sales this is my major and this is going to be some of the sales amount now after that I'm gonna create another major which would be of the gender based so for that I'm gonna paste one very simple code over here so let me go again there it over here and this is my Dex expression where I'm using sum X then I am using the filter of the fact tables where I'm saying related customer and see in selected value tax function I'm using here for the gender column and then I'm using the sales amount that is going to be my total sales amount because I'm using some X over here so this is the text function I'm going to use and this time it's going to return me the right resource so let me just select it over here or hit the enter button so before going that uh so let me just get it over there in the overview so this is the same page I just created where I can put my different measures in a formatted way and this is my card where I can bring my this newly created measure which is sales by gender and here you can see that and now if I'm gonna toggle between male and female It's Gonna Change so you can see that clearly now it is working and it is giving you the results as well so whenever you have to use your selected validx function and you want to create a table on the Fly then you have to combine it with any other Dex function then you can dynamically calculate whatever you are looking for over there now in this part I'm gonna get my total sales as well so here's my total sales over here you can see that my sales bar gender is coming as the header over here but if you would like to make the title dynamic as well you can do that but which is very easy so what you have to do you have to format this one to format you have to come here under the visual you will find the different options over there so you have to go to the title one and here title we are gonna clean and we are going to paste a new value over here but I haven't created that so let me first create so in order to make it Dynamic we have to create a text expression and for that we have to go again here to the table and then I'm gonna create a new measure so this measure again going to check dynamically if my selected value is my male gender or female gender and based on that it's going to return your value so over here you can see that clearly if my selected value equals to mail or M then it's going to say total sales by mails otherwise it's going to be total sales by females and you can customize it for the if you would like to again hit the enter button or click on this like button close this one and now I can go to this format pane over here under titles I can go to the Expressions here you have to bring this field value and you have to select over here the measure that you have created so let me get it here click OK and now you can see that I have selected female it's coming female but before going that I have to stop this category label over here now mail or female you can see the title has been changed over here so this is my total sales and this is this one but if you have noticed that my slicer is not changing this is and it's only because of the interactive action so I selected this I went into the format and here I selected edit instructions with the help of this you can filter or not filter any other resource and where you can see that it's not filtering out over there so that's the reason it's not filtering up so guys what do you think are you gonna apply it now do you have any idea why if I'm using only selected value text function and creating a table the table is creating but there is no data why it is not there and why it's working when I'm using with any other text function I hope now you have clear understanding about selected validx function and how you can use it to create a table and E1 to create Dynamic calculations but in case you have any other topic and you would like us to create any more videos please don't forget to comment in the comment section and also if you are looking for any of the power bi trainings please do let us know see you in the next video

Channel: BI Consulting Pro

Views: 604

Keywords: SELECTEDVALUE, SELECTEDVALUE DAX, DAX real-world, DAX use case, power bi, power bi tutorial, dax tutorial power bi, dax tutorial, bi consulting pro, dax series, dax videos, dax course, dax formulas for powerpivot, learn power bi, power bi training, power bi for beginners, power bi dax, power bi desktop, power bi course, dax for beginners, dax power bi, power bi data modeling, power bi hasonevalue, power bi tutorial for beginners, power bi interview questions and answers

Id: Pqr8JCd-Tok

Length: 10min 30sec (630 seconds)

Published: Sun Jun 11 2023