Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Logrige
Regular Visitor

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

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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_0-1670977629946.png

View solution in original post

6 REPLIES 6
FreemanZ
Super User
Super User

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_0-1670977629946.png

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

Logrige_1-1670983924791.png

 

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. 

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

Logrige_0-1671042915423.png

 

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
transform99
Resolver I
Resolver I

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.

transform99_2-1670975974108.png

 

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

transform99_3-1670976149640.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.