Forum Discussion

TonyGu's avatar
TonyGu
Icon for Helper I rankHelper I
4 years ago
Solved

DAX Column

I would like to have the Define column return 1, if ID only appear 1 time in that year. If that ID appear twice or more, return 1 for the earliest Month, and 0 for the rest month. Below is the result...
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can just check if the month is the first one that year or not.

     

    Try this as a calculated column:

    Define =
VAR _FirstMonth =
    CALCULATE (
        MIN ( Table1[MONTH] ),
        ALLEXCEPT ( Table1, Table1[ID], Table1[YEAR] )
    )
RETURN
    IF ( Table1[MONTH] = _FirstMonth, 1, 0 )
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

You can just check if the month is the first one that year or not.

 

Try this as a calculated column:

Define =
VAR _FirstMonth =
    CALCULATE (
        MIN ( Table1[MONTH] ),
        ALLEXCEPT ( Table1, Table1[ID], Table1[YEAR] )
    )
RETURN
    IF ( Table1[MONTH] = _FirstMonth, 1, 0 )