Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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" ) )
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
Hey Daniel,
just sent you a private message!
Many thanks,
Adam
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!
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
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" ) )
@AkhilAshok Works properly, thank you a lot for your help!!
Could you please help me with the same logic for Business Unit Level? For you it would take only a few minutes and it would save my life!
So if BU has critical and major it shows "red" in the Service Dashboard.
If a BU has critical it shows "red" in the Service Dashboard
If a BU has major it shows "yellow" in the Service Dashboard
If a BU has no incident it shows "green" in the Service Dashboard
Or in other words:
If a BU has red and yellow, then it should show red.
If a BU has green and yellow, then it should show yellow.
If a BU has green and red, then it should show red.
If a BU has only 1 colour, then that colour.
Thank you already in advance!
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" ) )
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |