Paula Guilfoyle CPA & MVP
A free video tutorial from Paula Guilfoyle CPA & MVP
Microsoft MVP, CPA Accountant, & Life Long Learner
4.4 instructor rating • 26 courses • 104,891 students

Lecture description

new to DAX, and not yet available in all versions of excel is SELECTED VALUES.  This can also be used to alter subtotals

Learn more from the full course

Mastering Intermediate DAX - Power BI, Power Pivot & SSAS

Take your Data Analysis Expression Skills to the Next Level with DAX for Power BI, Power Pivot and Analysis Services

01:57:46 of on-demand video • Updated May 2020

  • Carry out difficult DAX expressions in Power BI, Power Pivot and SSAS
  • Have the ability to modify the current filter context with DAX expressions
  • Have the ability to modify how totals and subtotals are shown
  • Explain and work with Context Transition in DAX
  • Work with hierarchies and related functions
  • Calculate different moving averages without using Time Intelligence functions
  • Work with advanced calculations using iterators
  • Understand Bidirectional filters
  • Advanced relationships and UseRelationship
English [Auto] In the last video we had a look at has one value and we're going to look at that later around in some of the activities. But in the latest version of Doc so docs of 2017 a new expression has been added to simplify the Haskell values and syntax and this function is selected value and its syntax is selected value and then table on the column the selected value and values functions. Read the current filter context not the word context. When you have a world context you just use a column reference. With really said in the case that it's a lookup table this time you're going to hop into Parel be-I to look at this example because I'm wanting an older version of Excel that doesn't have selected value but will look at the same sort of table as we did that we were looking at which has one value. Or in the power pivot Datamart all this time because the selected values is only available in the latest versions of docs and I'm running an older version of Excel and that we have this time is we have our customer categories and we have a total value. We also has one value. Now if we drill in we have the pillowy sales numbers of the sales person ID is in here. We've used the house value function and we can see or has one value function is slightly different to what you looked at in the exile model a few minutes ago. Because this time in our pivot table we have the sales person Id so because we have the sales person Id We want to see if there is one value selected for that particular column. Let's have a look now and see what selected values will do. So if we just start with selected values and it is asking us for our column name and let's go to our invoice table and let's go to our sales person ID and I'm just going to close the back there and I'm going to add that measure into our pivot table. Can see when we measure a be renamed the measure as well to selected value. What we can see is when we use selected value it pulls back the actual value that has been selected. So the sales person ID that has been selected here is 2. And in this row it's tree and then the last row is 20. So it's actually returning back to you. The value of that particular cell and it's allowing you see that there is one value there whereas up here in computer store for this calculation no value has actually been selected. Now of course you can use an if statement were all selected so that instead of returning the actual value you can return the total profit. So let's have a look at calculating that. Now using an actual If statements will have a new measure and we will say if selected value for the column name on the invoice table for the sales person ID. So if that particular column is not equal to blank is not equal to blank what we want to happen. Well if it's not equal to blank then we do want our total profit. Otherwise we just want blank. We can hit enter and close our black hair and we can at this measure now into our pivot table and we'll rename that as well-selected value too and we can see very quickly that by using selected file you would get the same result as we did with has one value. So there's quite a few options that you can use when you're trying to have a different drilled true value so your totals and your subtotals are slightly different to the values when you actually drill through and so far we've we've looked at is filtered is cross filtered is all selected and we've also looked at selected values at the end of the section you're going to find some activity so you can practice all of this stuff and if you don't understand please do return to the videos and watch them again and drop me a query or a question with the difficulty that you're having.