Forum Discussion
DAX IF Function with 2 connected tables
Hey guys,
I'm new to the world of DAX Formulas and would need a bit of a help with an "IF" Function.
I have 2 tables that are connected to each other based on service ID: "Service Dashboard" and "Incident Report".
What I want to do is making a coloumn called Service Status into the Service Dashboard table with the following conditions:
- if a service has a Critical Incident it should show/write "red"
- if a service has a Major Incident it should show/write "yellow"
- if a service has both Major and Critical Incident it should show/write "red"
Could you please help me out?
Any help is greatly appreciated!
Many thanks!
Adam
A calculated column like this might help:
BU Status = VAR BU = 'ServiceDashboard'[Business Unit] VAR HasRed = CONTAINS ( 'ServiceDashboard', 'ServiceDashboard'[Business Unit], BU, 'ServiceDashboard'[Service Status], "red" ) VAR HasYellow = CONTAINS ( 'ServiceDashboard', 'ServiceDashboard'[Business Unit], BU, 'ServiceDashboard'[Service Status], "yellow" ) RETURN IF ( HasRed, "red", IF ( HasYellow, "yellow", "green" ) )
10 Replies
What is the relationship cardinality between "Service Dashboard" and "Incident Report"? One to many or Many to one?
It is one to many, 1 Service can have multiple incidents.
I tried to make a lookupupvalue, but is says: A table of multiple values was supplied where a single value was expected.
Column 2 = LOOKUPVALUE('Incident Report'[FieldValuesAsText.ASPStatus];'Incident Report'[LookupToServiceDashboardId];ServiceDashboard[Id])
Many thanks!
Hey guys,
I would like to ask a question conerning a lookup value function: as I'm getting the following error message:
"A table of multiple values was supplied where a single value was expected"
What I wanna achieve:
To the “Service Dashboard” table the following “coloumn2" should be included:
- If the service has a critical incident “red” should be written
- If the service has a major incident “yellow” should be written
- If the service has both major and critical incident “red” should be written
currently my formula looks like this:
Column 2 = LOOKUPVALUE('Incident Report'[FieldValuesAsText.ASPStatus];'Incident Report'[LookupToServiceDashboardId];ServiceDashboard[Id])
Any help is appreciated!
Many thanks,
Adam
Hi Anonymous,
Could you please share some sample data to me? You could upload your report to your OneDrive and send the link to me.
Regards,
Daniel He
