Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
MaxDate Measure = CALCULATE(MAX('Table'[DOB]), ALLSELECTED('Table'[DOB]) )
Diff Measure = DATEDIFF ( MIN ( 'Table'[DOB] ), [MaxDate Measure], DAY )
MaxDate Measure = CALCULATE(MAX('Table'[DOB]), ALLSELECTED('Table'[DOB]) )
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 ?
Diff Measure = DATEDIFF ( MIN ( 'Table'[DOB] ), [MaxDate Measure], DAY )
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 ?
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!
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 ?
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!
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |