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.
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 ?
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 ?
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!
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 ?