Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Sean
Community Champion
Community Champion

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

View solution in original post

Sean
Community Champion
Community Champion

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

View solution in original post

8 REPLIES 8
Sean
Community Champion
Community Champion

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 ?

Sean
Community Champion
Community Champion

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.  

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 ?

 

Sean
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 ?

Sean
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.