Create a Measure on Text Field

[ldwf]

Hello, I have a star schema PBI model.  I have a dimension table with comment field,.  I have a date slicer where the user can select multiple dates from a date dimension table.  I want to use the maximum date selected in the slicer and apply this date to filter the dimension table of comments such that I extract the comment which applies to the date selected in the slicer.  I tried creating a measure (on this comment field) using DAX.  My DAX formula always brings back the MAX comment value, but not the MAX comment value corresponding to the maximum date selected in the slicer.   I only want one row retuned, which would correspond to the maximum date selected in the slicer and show the comment for that date.  Should I be creating a calculated field instead?  This is my measure formula:

LatestComment=CALCULATE(MAX('Dim_Comments'[Comments]),FILTER('Dim_Date','Dim_Date'[Date]=MAXX(ALLSELECTED('Dim_Date'[Date]), 'Dim_Date'[Date])))

[tamerj1]

@ldwf 

Please use

LatestCommentMeasure =
CALCULATE (
    MAX ( 'Dim_Comments'[Comments] ),
    'Dim_Comments'[Date] = MAX ( 'Dim_Date'[Date] )
)

[ldwf]

I have tried this before as well, but it always returns the maximum comment, not the comment corresponding to the maximum date selected in the slicer.

[tamerj1 · Accepted solution]

@ldwf 

Except you were filtering the Dim_Date[Date] column while you should be filtering the Dim_Comments[Date] column

[ldwf]

That was it!

[ldwf]

yes.  the dim_comment table has all the comments over the course of time. The comment can be different from one month to the next.  But I want my result to only show one row, i.e, the comment where the date matches the maximum date selected in the slicer.

[tamerj1]

Hi @ldwf 

Do you have column in the Dim_Comment table?