Forum Discussion

GJ24's avatar
GJ24
New Member
1 year ago
Solved

IF and RETURN syntax error

Hi,

I'm getting an syntax error with the below DAX measure.

 

I basically want the title to show Month and Year if one month and year is selected or from Month and Year to Month and Year if more than one monthis selected on a slicer.

 

Any suggestions on how this is written is very welcome.

 

S&M Select Title = 

VAR _MMMYY = FORMAT(MAXX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

VAR _Max = FORMAT(MAXX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

VAR _Min = FORMAT(MINX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

IF(
    ISFILTERED(Dim_Dates[Month Short]) && ISFILTERED(Dim_Dates[Year])

RETURN
    _MMMYY,
    RETURN
	_Min&"to"&_Max
)

 

 

Need Help
Show and Tell
  • Fowmy's avatar
    Fowmy
    1 year ago

    GJ24 

    Try this way:

    S&M Select Title =
VAR _Max =
    FORMAT ( MAXX ( ALLSELECTED ( Dim_Dates ), Dim_Dates[Date] ), "MMM-YYYY" )
VAR _Min =
    FORMAT ( MINX ( ALLSELECTED ( Dim_Dates ), Dim_Dates[Date] ), "MMM-YYYY" )
VAR _Result =
    IF ( _Min = _Max, _Min, _Min & " to " & _Max )
RETURN
    _Result



  • mark_endicott's avatar
    mark_endicott
    1 year ago

    GJ24 - Ok, should work if you add spaces to the final clause:

     

    VAR _MMMYY = FORMAT(MAXX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

VAR _Max = FORMAT(MAXX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

VAR _Min = FORMAT(MINX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")
RETURN
IF(
    ISFILTERED(Dim_Dates[Month Short]) && ISFILTERED(Dim_Dates[Year])
    _MMMYY,
    _Min & "to" & _Max
)

     

    If this does not, please send a screenshot with the error message. 

     

    If this works for you please accept as the solution to help others with the same challenges. 

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    1 year ago

    GJ24 

    Try this way:

    S&M Select Title =
VAR _Max =
    FORMAT ( MAXX ( ALLSELECTED ( Dim_Dates ), Dim_Dates[Date] ), "MMM-YYYY" )
VAR _Min =
    FORMAT ( MINX ( ALLSELECTED ( Dim_Dates ), Dim_Dates[Date] ), "MMM-YYYY" )
VAR _Result =
    IF ( _Min = _Max, _Min, _Min & " to " & _Max )
RETURN
    _Result



  • mark_endicott's avatar
    mark_endicott
    Super User
    1 year ago

    GJ24 - try this:

     

    S&M Select Title = 

VAR _MMMYY = FORMAT(MAXX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

VAR _Max = FORMAT(MAXX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

VAR _Min = FORMAT(MINX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")
RETURN
IF(
    ISFILTERED(Dim_Dates[Month Short]) && ISFILTERED(Dim_Dates[Year])
    _MMMYY,
    _Min&"to"&_Max
)

     

    If this works for you please accept as the solution to help others with the same challenges. 

    • GJ24's avatar
      GJ24
      New Member
      1 year ago

      Hi mark_endicott 

      Thanks for this, but this doesn't work. If you think of anything else please let me know.

      • mark_endicott's avatar
        mark_endicott
        Super User
        1 year ago

        GJ24 - Ok, should work if you add spaces to the final clause:

         

        VAR _MMMYY = FORMAT(MAXX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

VAR _Max = FORMAT(MAXX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")

VAR _Min = FORMAT(MINX(ALLSELECTED(Dim_Dates),Dim_Dates[Date]),"MMM-YYYY")
RETURN
IF(
    ISFILTERED(Dim_Dates[Month Short]) && ISFILTERED(Dim_Dates[Year])
    _MMMYY,
    _Min & "to" & _Max
)

         

        If this does not, please send a screenshot with the error message. 

         

        If this works for you please accept as the solution to help others with the same challenges.