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 )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

TonyGu Maybe:

Define =
  VAR __ID = [ID]
  VAR __Year = [Year]
  VAR __Month = [Month]
  VAR __MinMonth = MINX(FILTER('Table',[ID] = __ID && [Year] = __Year),[Month])
  VAR __Table = SUMMARIZECOLUMNS('Table'[ID],'Table'[Year],"__Count",COUNTROWS('Table'))
RETURN
  SWITCH(TRUE(),
    COUNTROWS(FILTER(__Table,[ID] = __ID && [Year] = __Year)) = 1,1,
    __Month = __MinMonth, 1,
    0
  )