Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX Calculated Column Based On Other Tables

Hi,

Is it possible to use DAX to add a column to a table that filters based on values in a related table?

Say I have a table of Opportunities [Opportunity], with a column called status. Records in Opportunity may or may not have a lookup to one or multiple Projects [Project] based on a lookup column called projectid. Projects have a statecode column that has values of "Active" or "Inactive". 

How can I write a DAX column that says "If this Opportunity has status = "Won" AND any related Project with statecode "Active" then give it the value "Current" for my calculated column."

 

Thanks for any help you can provide!

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous yes it can be done but the solution depends on how these tables are related? Can you share a relationship diagram and which tables contain what column that you need DAX expression on?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I will describe it in more accurate detail:

       

       

      1 Opportunity can have many Projects.

      The calculated column will be on Opportunity. It will check values in multiple Opportunity columns and 1 Project column.

      If [Opportunity].stagename = "Qualify" then "Initial"

      If [Opportunity].stagename = "Close" AND [Opportunity].presentpropose = true then "SOW"

      If [Opportunity].bsi_completedtechnicalpresales = true then "Technical Presales"

      If [Opportunity].conducteddemo = true then "Demo"

      If [Opportunity].developproposal = true then "Proposal Developed"

      If [Opportunity].statuscode_display = "Won" and ANY RELATED([bsi_project].statecode_display = "Active" then "Active"

       

      I will probably have to clean up any holes in my logic, but I hope you can see the gist of what I'm trying to do.