Reply
Regular Visitor
Posts: 26
Registered: ‎02-18-2016
Accepted Solution

Dax Calculation Help

[ Edited ]

Can someone help me please write this DAX measure:

Dax Measure.PNG

 

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.


Accepted Solutions
Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: Dax Calculation Help

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

View solution in original post

Highlighted
Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: Dax Calculation Help

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

View solution in original post


All Replies
Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: Dax Calculation Help

MaxDate Measure = CALCULATE(MAX('Table'[DOB]), ALLSELECTED('Table'[DOB]) )
Regular Visitor
Posts: 26
Registered: ‎02-18-2016

Re: Dax Calculation Help

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 ?

Highlighted
Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: Dax Calculation Help

Diff Measure = DATEDIFF ( MIN ( 'Table'[DOB] ), [MaxDate Measure], DAY )
Regular Visitor
Posts: 26
Registered: ‎02-18-2016

Re: Dax Calculation Help

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.  

1.JPG

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:

2.JPG

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 ?

 

Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: Dax Calculation Help

[ Edited ]

@aghonaim

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] )

Column Chart - Age Group X-Axis.gif

Hope this helps! Smiley Happy

Regular Visitor
Posts: 26
Registered: ‎02-18-2016

Re: Dax Calculation Help

[ Edited ]

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

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 ?

Super Contributor
Posts: 1,984
Registered: ‎08-11-2015

Re: Dax Calculation Help

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! Smiley Happy

Regular Visitor
Posts: 26
Registered: ‎02-18-2016

Re: Dax Calculation Help

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!