Capture date from Date Slicer

Power BI
14

@axaeffect,

I’ll try to answer all of your questions above and in this post, since they are closely related.

I can tell from the problems you’re having with your measures that you need to develop a better understanding of evaluation context, filter context and row context. These are the foundational principles of all DAX calculations, and even with focused study they take a while to fully sink in. I think the best place to start is with this course:

focusing particularly on all the sections related to context, CALCULATE, and table functions. If you’re anything like me, you’ll probably have to watch these over and over and over again as you work through your Power BI projects. However, one day it all will click into the place, and you’ll feel like this:

The next foundational concept to focus on is “measure branching”. In your second post, you are on the right track, but you’re trying to do too much within a single measure. In measure branching, each measure typically does one specific thing, and they build logically upon each other, in increasing complexity. This video does a great job explaining the concept and providing examples:

OK, on to your specific questions:

  1. What is the “Aging with Total” measure doing?

image
image793×607 40 KB

  1. filtering your fact table, based on the selected date range harvested from your slider
  2. making a virtual copy of your filtered fact table
  3. adding a virtual column to 2) with the results of your Aging measure for each row of the virtual table
  4. checking to see if each row of the matrix has a single value for the SalesOrderNumber field
  5. if yes (which will be true for every row that isn’t the final total row), it just applies the Aging measure
  6. if no (meaning we are in the total row of the matrix), it takes the sum of the entire column virtual aging column we added in step 3)

This is all necessary since there is no proper evaluation context for the regular Aging measure to operate on in the total row.

image

  1. What is SELECTEDVALUE doing?

There’s not a specific Enterprise DNA video on this function, but I think the following provides a good explanation:

  1. Aging Category Measure

You had the right idea here, but didn’t need the CALCULATE statement, nor the variable/SELECTEDVALUE construct. In this case, just let the evaluation context and measure branching do most of the work, and use a simple IF statement:

Aging Category Revised = 
IF(
    [Aging with Total] <=1,
    "small",
    "big"
)

image

Hope this answers your questions. Full solution file posted below.

Work through the videos above, and if you run into other problems, just give a shout.

1 Like