Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi! I have two sources of data like the following, in a one to many relationship using ID:
Data
ID | Value |
XY | $1 |
AB | $2 |
CZ | $3 |
Activities
Status | ID |
Complete | AB |
In Progress | XY |
Waiting | XY |
Complete | AB |
Waiting | CZ |
Complete | XY |
Complete | CZ |
I would like to have a single value in Data for Status, so that In Progress will be shown if there is any to the corresponding ID, otherwise Waiting, and if neither then Complete, as follows:
Data
ID | Value | Status |
XY | $1 | In Progress |
AB | $2 | Complete |
CZ | $3 | Waiting |
The goal is to display this in a table in the report, highlighting the progress of connected activities. There are 6 different status types (not started, etc). Essentially I want a column like this in Data, but don't know the formula:
IF 'Activities'[Status] CONTAINS "In Progress", "In Progress", IF Activities'[Status] CONTAINS "Waiting", "Waiting", IF 'Activities'[Status] CONTAINS "Complete", "Complete", ""
Is there a simple way to set this up?
Solved! Go to Solution.
Hi @Alex_ ,
Create a calculated column in Status Table:
Status Check = CALCULATE(CONCATENATEX('Status','Status'[Status],",",'Status'[Status],ASC),ALLEXCEPT('Status','Status'[ID]))
Flag Check = LOOKUPVALUE('Status'[Status Check],'Status'[ID],Data[ID])
Value Status =
Var Complete=SEARCH("Complete",Data[Flag Check],,-1)
Var InProgress=SEARCH("In Progress",Data[Flag Check],,-1)
Var Waiting=SEARCH("Waiting",Data[Flag Check],,-1)
var check=IF(InProgress<>-1,"In Progress",IF(Waiting<>-1,"Waiting",IF(Complete<>-1,"Complete",BLANK())))
return check
Hi @Alex_ ,
Create a calculated column in Status Table:
Status Check = CALCULATE(CONCATENATEX('Status','Status'[Status],",",'Status'[Status],ASC),ALLEXCEPT('Status','Status'[ID]))
Flag Check = LOOKUPVALUE('Status'[Status Check],'Status'[ID],Data[ID])
Value Status =
Var Complete=SEARCH("Complete",Data[Flag Check],,-1)
Var InProgress=SEARCH("In Progress",Data[Flag Check],,-1)
Var Waiting=SEARCH("Waiting",Data[Flag Check],,-1)
var check=IF(InProgress<>-1,"In Progress",IF(Waiting<>-1,"Waiting",IF(Complete<>-1,"Complete",BLANK())))
return check
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
70 | |
68 | |
43 | |
34 | |
26 |
User | Count |
---|---|
86 | |
49 | |
45 | |
38 | |
37 |