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 

 

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:
Logrige's avatar
Logrige
Regular Visitor
3 years ago

Wow thank you so much. 

Would this work if the table is formed from a relationship of other tables? I formed a relationship between three tables to get the data. Not only that, the storage mode is direct query from different sql databases. 

 

When I create column using code I get a warning that measure formula refers to a column that contains many values...

 

  • 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!!