cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

 

 

 

 IF_Function_Service_Status.JPG

1 ACCEPTED 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" ) )

View solution in original post

10 REPLIES 10
v-danhe-msft
Microsoft
Microsoft

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hey Daniel,

 

just sent you a private message!

 

Many thanks,

 

Adam

AkhilAshok
Solution Sage
Solution Sage

 What is the relationship cardinality between "Service Dashboard" and "Incident Report"? One to many or Many to one?

Anonymous
Not applicable

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!

 

relationship.jpg

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" )
Anonymous
Not applicable

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

 

 

 

No Incident.JPG

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" ) )
Anonymous
Not applicable

@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

 

Business_Unit.JPG

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" ) )

View solution in original post

Anonymous
Not applicable

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

 

vlookup_if.JPG

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.