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:
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Brute force-ish (someone may come up with a much more elegant way to iterate through multiple primary key references by using a unique string)..

 

Long way (split data into 2 tables):

1) duplicate your data source in transformation

2) in 1 table filter status column to Archived, in the other filter to In Serivce (splits out different data, but keeps primary key).

3) Make sure date data type is set to Date.

4) Change names of tables to Archive/In Service so you know which ones are different.

 

In the In Service Table I created a custom column:

Setting = 
IF (
    AND (
        ISBLANK ( LOOKUPVALUE ( Archive[Date], Archive[Device], 'In Service'[Device] ) ),
        ISBLANK ( 'In Service'[Date] )
    ),
    "Missing",
    IF (
        AND (
            NOT (
                ISBLANK ( LOOKUPVALUE ( Archive[Date], Archive[Device], 'In Service'[Device] ) )
            ),
            ISBLANK ( 'In Service'[Date] )
        ),
        "Error",
        IF (
            AND (
                NOT (
                    ISBLANK ( LOOKUPVALUE ( Archive[Date], Archive[Device], 'In Service'[Device] ) )
                ),
                NOT ( ISBLANK ( 'In Service'[Date] ) )
            ),
            "Refresh",
            "No Condition Set"
        )
    )
)

 

Output is below