Forum Discussion

TonyGu's avatar
TonyGu
Helper 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 )
Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

Hello:

Give this a try. Hope it helps..

 

Define =
var yr = Data[Year]
var countinyear = COUNTROWS(
FILTER(Data, Data[Year] = yr))
return
IF(countinyear = 1 || Data[Mo] = 1,1,0)
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

You seem to be assuming that January is always the first month for each year with multiple rows in the Data table (e.g. this would not work if the first D row in the given example were deleted).