Forum Discussion
DAX IF Function with 2 connected tables
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" ) )
Solution SageWhat 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
- AkhilAshok
How about a calculated column in ServiceDashboard table like this:
Service Status = VAR HasCritical = CALCULATE ( CONTAINS ( 'Incident Report', 'Incident Report'[FieldValuesAsText.ASPStatus], "Critical Incident" ) ) VAR HasMajor = CALCULATE ( CONTAINS ( 'Incident Report', 'Incident Report'[FieldValuesAsText.ASPStatus], "Major Incident" ) ) RETURN IF ( HasMajor && NOT ( HasCritical ), "yellow", "red" )Many thanks for the help, it's almost totally fine! Unfortunately it shows "red" to the services, where no incident occured. Would it be possible to get "green" as an answer?
Thank you again!
Adam
- AkhilAshok
How about this:
Service Status = VAR HasCritical = CALCULATE ( CONTAINS ( 'Incident Report', 'Incident Report'[FieldValuesAsText.ASPStatus], "Critical Incident" ) ) VAR HasMajor = CALCULATE ( CONTAINS ( 'Incident Report', 'Incident Report'[FieldValuesAsText.ASPStatus], "Major Incident" ) ) RETURN IF ( HasCritical, "red", IF ( HasMajor, "yellow", "green" ) )