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 device STATUS. One "In Service" and the other  "Archive". 

I would like to create a formula that will display "Missing" if the DATE is blank for both "In Service" and "Archived" for the same device. 

 

DEVICE       EQUIPMENT    STATUS          DATE   
X1RXEIn Service 
X1PERArchived 
X2BEKIn Service 
X2RXEArchived12/01/2022
X3RXEIn Service 
X3RXEArchived 
X4WEIn Service 
X4PERArchived 
X5RXEIn Service11/01/2022
X5BEKArchived10/01/2022

 

For example, the table bellow, the DATE column is blank for both rows. I would like SETTING column to display "Missing" for X1.

 

DEVICE     EQUIPMENT    STATUS     DATE          SETTING  
X1RXEIn Service Missing
X1PERArchived Missing

 

For the next example, the device Archived has a value in the DATE column but not in the In Service column then it should display Error. 

DEVICE  EQUIPMENT   STATUS   DATE               SETTING  
X2BEKIn Service Error
X2RXEArchived12/01/2022Error

 

Lastly, if both In Service and Archived have Dates then the column should display Refresh

 

DEVICE  EQUIPMENT   STATUS   DATE               SETTING  
X5RXEIn Service11/01/2022Refresh
X5BEKArchived10/01/2022Refresh

 

Is this possible in Power Bi? I'm very new to this program. 

 

Thanks!! 

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:

6 Replies

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

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

  • 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