Forum Discussion

Logrige's avatar
Logrige
Regular Visitor
3 years ago
Solved

Custom column for duplicate condition

I would like to create a formula for a new column named SETTING that will either say Error, Missing, or Refresh depending on the conditions met.    For the table below, there are two rows for each ...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Logrige 

     

    try to add a column with this:

    Setting =
    VAR CurrentDevice = TableName[DEVICE]

    VAR _ValueInService =
    MINX(
            FILTER(
              TableName,
              TableName[DEVICE] = CurrentDevice
                  &&TableName[STATUS]="In Service"
            ),
        TableName[DATE]
    )

    VAR _ValueArchived =
    MINX(
         FILTER(
              TableName,
              TableName[DEVICE] = CurrentDevice
                &&TableName[STATUS]="Archived"
        ),
        TableName[DATE]
    )

    RETURN
    SWITCH(
        TRUE(),
        _ValueInService<>BLANK()&&_ValueArchived<>BLANK(),
        "Refresh",
        _ValueInService=BLANK()&&_ValueArchived<>BLANK(),
        "Error",
        _ValueInService=BLANK()&&_ValueArchived=BLANK(),
        "Missing",
        BLANK()
    )
     
    i tried and it worked like this:
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

This calculated column formula works

Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[DEVICE]=EARLIER(Data[DEVICE])&&Data[DATE]<>BLANK()))=1,"Error",if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[DEVICE]=EARLIER(Data[DEVICE])&&Data[DATE]<>BLANK()))=2,"Refresh","Missing"))

Hope this helps.