Custom column for duplicate condition

[Logrige]

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
X1	RXE	In Service
X1	PER	Archived
X2	BEK	In Service
X2	RXE	Archived	12/01/2022
X3	RXE	In Service
X3	RXE	Archived
X4	WE	In Service
X4	PER	Archived
X5	RXE	In Service	11/01/2022
X5	BEK	Archived	10/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
X1	RXE	In Service		Missing
X1	PER	Archived		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
X2	BEK	In Service		Error
X2	RXE	Archived	12/01/2022	Error

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

DEVICE	EQUIPMENT	STATUS	DATE	SETTING
X5	RXE	In Service	11/01/2022	Refresh
X5	BEK	Archived	10/01/2022	Refresh

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

Thanks!! 

[FreemanZ · Accepted solution]

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]

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]

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]

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

[Ashish_Mathur]

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.

[transform99]

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