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:
FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi Logrige 

 

how are the tables related?

 

It gives a warning, because the row context provided by FILTER does propagate to other tables. Some RELATED/RELATEDTABLE might be needed, if your model support the expected calculations. 

Logrige's avatar
Logrige
Regular Visitor
3 years ago

oh I think I'm understnading. In this case cymrecloser is related to pb_equipment with DEVICE many to 1 and pb_equipment is related to mapping_view_recloser thru global_id many to 1. I'll play around with this to see if I can get it to work. 

Thanks!!