cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Dax Calculation Help

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.

2 ACCEPTED SOLUTIONS
Community Champion
Community Champion

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

View solution in original post

Community Champion
Community Champion

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

View solution in original post

8 REPLIES 8
Community Champion
Community Champion

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

View solution in original post

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 ?

Community Champion
Community Champion

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

View solution in original post

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 ?

 

Community Champion
Community Champion

@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

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 ?

Community Champion
Community Champion

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

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!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors