SELECTEDVALUE DAX function - Power BI Tutorial

- In Power BI, we can set up filters in the queries to the data sources in Power Query, and once we load the data into Power BI, we can configure data model filters with pivot coordinates, conditions within DAX formulas, and through the filters pane. We can also set up filters using the SelectedValue DAX function. The SelectedValue function references a single column in the data set. The SelectedValue function returns a DAX measure. We can then use this measure in other more complex calculations in our Power BI models. SelectedValue is a user-controlled input, which also means we need to add the same field in the formula to a slicer visual within Power BI. It also lets us set up disconnected tables within our model. In Power BI, lets create another measures group table for the second iteration of our loan model measures, which will build on the calculations we set up for Model One. We'll choose to enter data, then give the new table the name, Model Two. Next, we'll use the SelectedValue DAX function, with the field for the selected date, from the interest rate data table. We'll then remove column one from this table so it turns into a measures group. Let's first change this selected date measure to a short date format. Next, we'll put this new measure in a card visual. We see that we do not get a value return for this, because we don't have any dates selected right now. To enable a selected date measure to work, we need to create a user controlled slicer for the end user to select the date of the interest rate table they want to use in the rest of the analysis. We also want to set up the selection controls for the slicer, so the end user can only select one date from a dropdown menu. Now, when we select a date from the slicer, we see the card visual date updates according to the date we're choosing in the slicer. We want to set up Model Two for the loan calculations in Power BI, so that we can select any date from the interest rate table, and that is when the rate is locked in for the loan. Let's add a new table visual to the same page. We'll then add the dates from the dates table created with the DAX formula to the values, so they display as a short date in the first column of the table. It's important to note that these dates aren't connected to the dates in the interest rate table. Because we're going to start our loan calculations on the first of the month in the month after our selected interest rate date, let's filter this table so it only displays the dates where the day is equal to one. We can also make the text a bit bigger through the options in the grid sub menu on the visual formatting options. It's also helpful to go into the model view to confirm the dates table we just added to our table, and the interest rate data table we're using in the slicer, aren't connected. Well, there are many cases where we do want the slicer field connected to the table we're using in a visual like this, we don't want these model tables connected, because that will enable us to project the loan model into the future on a date timeline. With the dates in the table, let's add our selected date measure to this table visual. You see, it returns the same selected date in the slicer for each of the pivot coordinates in this table, which are the dates we just added to the values. As we change the dates in the slicer, we see the dates updates simultaneously, as well. The reason we don't see the date from the selected date measure match the dates in the first column of our table, is because the data tables themselves aren't connected. The trick to getting these disconnected tables set up, is to think about how they will interact with one another as we create the loan calculations for Model Two, which will reference this selected date and the calculations. Let's also take a closer look at the interest rates data table to make sure we're including the dates in the selected date measure that we want, as well. We can see that the 15-year rate is lower than the 30-year fixed mortgage rate for each weekly date that the public data set tracks both these interest rates. Also, notice the 30-year fixed rate starts before the 15-year rate, by about 20 years. Let's only include the dates in this interest rate data table where we have data points for both rates. There are a few different ways we can remove the rows with nulls from the data table, but let's do this in the power query editor, so we don't have to worry about making sure our model filters account for them. In the interest rate data query, let's remove any of the rows with nulls from the 15-year fixed rate column, then load the data table back into Power BI. We can choose any of the steps in the applied steps and apply the filter from there. We now see our interest rate data table has both the 15-year and the 30-year fixed mortgage for each of the dates.