- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Dax Calculation Help

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2017 11:31 PM - edited 03-18-2017 11:37 PM

Can someone help me please write this DAX measure:

I want the MaxDate measure to return "7/2/2002" (which is the max DOB within the selected range by the slicer) for every single record selected. My DAX measure "MaxDate = CALCULATE(MAX([DOB]))" returns the max date in the whole DOB column.

Solved! Go to Solution.

Accepted Solutions

## Re: Dax Calculation Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2017 08:19 AM

MaxDate Measure = CALCULATE(MAX('Table'[DOB]), ALLSELECTED('Table'[DOB]) )

## Re: Dax Calculation Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2017 10:12 PM

Diff Measure = DATEDIFF ( MIN ( 'Table'[DOB] ), [MaxDate Measure], DAY )

All Replies

## Re: Dax Calculation Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2017 08:19 AM

MaxDate Measure = CALCULATE(MAX('Table'[DOB]), ALLSELECTED('Table'[DOB]) )

## Re: Dax Calculation Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2017 01:22 PM

Great! thanks Sean so much. It works exactly the way I want.

Now, I need help with the second problem: when I try to calculate the difference between the Max Date Measure and DOB using both a new Diff Meausre or Diff Calculated Column, I get the following errors:

1- Diff Measure = Table[MaxDate]- Table[DOB]

A single value for column 'DOB' in table 'Waitlist' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

2- Diff Calculated Column = Table[MaxDate]- Table[DOB]

A circular dependency was detected.

Any clue how to get the difference between the Max Date and DOB for each record selected within the range using the slicer ?

## Re: Dax Calculation Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2017 10:12 PM

Diff Measure = DATEDIFF ( MIN ( 'Table'[DOB] ), [MaxDate Measure], DAY )

## Re: Dax Calculation Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-25-2017 08:13 PM

Great! thanks again Sean for your help ... it works exactly the way I want.

Now my last challange is now as follows:

I use this Diff Measure to calcualte the age group (category) for each indivdual case based on another age group range table with min and max range for each age group. I calcualte that using another measure called AgeGroup and it works perfectly fine ... it determines the age group for each case dynmically as I move the DOB slicer as you see in the visual table.

The challange now is I can't use the AgeGroup Measure as the X Axis in the bar chart to show the count of each AgeGroup dynmically as I move the slicer to match the counts I have in the table to look like that:

Is there any way to do that ? any work around ? or any special custome visual from the library that can allow me to use the calcualted measure in the X Axis ?

## Re: Dax Calculation Help

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-25-2017 09:03 PM - edited 03-25-2017 09:13 PM

You'll need to convert the above 2 Measures to **Columns**

then also your **Age Group** (buckets/bins) should be a **COLUMN**

(this way you can also use as **AgeGroup** a Slicer and/or for the Legend - you can't do either of those with a Measure!)

Then create a simple **Measure**

Count of Age Group = COUNTA ( Table[Age Group COLUMN] )

Hope this helps!

## Re: Dax Calculation Help

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-25-2017 09:41 PM - edited 03-25-2017 09:42 PM

Wow that looks very promising and thanks Sean for your quick response!

However when I tried to convert the first MaxDate Measure to a calculated column using the same formula you shared with me earlier: MaxDate Measure = CALCULATE(MAX('Table'[DOB]), ALLSELECTED('Table'[DOB]) ) I get the famous error: A circular dependency was detected.

I'm sure I'll get another error when I convert the second measure: Diff Measure = DATEDIFF ( MIN ( 'Table'[DOB] ), [MaxDate Measure], DAY ) to a caculated coulmn too.

Do I need to tweak both formulas to convert them from measures to calculated columns ?

## Re: Dax Calculation Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-25-2017 11:58 PM

Max Column = MAX ( 'Table'[DOB] ) Diff Column = DATEDIFF ( 'Table'[DOB], 'Table'[Max Column], DAY )

You don't need the aggregators for the columns

That should do it!

## Re: Dax Calculation Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2017 12:37 AM

But this will bring the max DOB in the entire coulmn and the diffference with it not the max and the diff of the range selected in the slicer!