Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

 

 

 

 

Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    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's avatar
    AkhilAshok
    Solution Sage
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hey Daniel,

       

      just sent you a private message!

       

      Many thanks,

       

      Adam