Forum Discussion

adaxx's avatar
adaxx
New Member
3 years ago

Need help on this DAX error message

My logic is this: 

If variable date2 is not blank for this each ID, then output date2.

If date2 is blank, then output a random number between 0 and the most recent month of column date1.

 

So the sample data is like this:

IDdate1date2output
12023-02-2833
22023-05-31 5
32022-12-3100
42023-04-3011
52023-01-3144
62023-03-31 5

 

This is my function:

MO = IF(ISBLANK('table'[Date1])=False,'table'[Date1],RANDBETWEEN(0,(max(month('table'[date2]))))), but it keeps giving me error: The MAX function only accepts a column reference as an argument.
 
Can someone help me with this? Thank you!!!
Need Help

2 Replies

  • v_mark's avatar
    v_mark
    Helper V
    3 years ago

    Try this. You might need to tweak the calculated column measure a little bit to tailor fit your needs. 

    Output Measure =
    IF(
        NOT(ISBLANK('Table'[date2])),
        'Table'[date2],
        RAND() * EOMONTH(MAX('Table'[date1]), 0)
    )
  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi adaxx ,

     

    MAX function accepts either a column

    =
MAX ( table[column] )


    or two scalar  values

    =
MAX ( 1, 2 )

    =
MAX ( MONTH ( table[date column] ), 100 )

    This part of your formula retuns just ONE scalar value

    =
MONTH ( 'table'[date2] )

    Below is your formula modified so the syntax is correct but I am not sure what you are trying to achieve here. You also did not mention if it is a measure or a calc column

    MO =
IF (
    ISBLANK ( 'table'[Date1] ) = FALSE,
    'table'[Date1],
    RANDBETWEEN ( 0, MONTH ( 'table'[date2] ) )
)