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
- AkhilAshok
Solution Sage
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
v-danhe-msftMicrosoft Employee
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