Forum Discussion

lbudack's avatar
lbudack
Advocate III
5 years ago
Solved

SQL Statement to DAX - JOIN & CASE

Is there a way to write this in DAX? I thought I knew DAX better than SQL, but I guess not. I've been all over these forums looking for solutions, but I can't get it to work the way it does in SQL. 

 

 

SELECT DISTINCT Organization.OrgName,
CASE 
	 WHEN ProjectNumber IS NOT NULL THEN 'Yes' ELSE 'No'
	 END AS Prospect
FROM Project
     RIGHT OUTER JOIN Organization ON Project.ClientOrgID = Organization.OrgID

 

 

Thanks in advance, DAX superstars!  

Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi lbudack ,

     

    Create a calculated column like so:

    Prospect? = 
IF (
    SUMX (
        'Project Table',
        FIND ( 'Org Table'[OrgID], 'Project Table'[OrgID],, 0 )
    ) > 0,
    1
)

     

     

    Best regards

    Icey

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    Hey lbudack ,

     

    the answer is a little more complex than "Of course you can!" 🙂

    But before I try this, here is a link that provides an introduction into Power BI data models, it's important to always be aware that the data model is king. The link

    Now my attempt to answer your question, there is an underlying assumption. Your data model contains two tables both are related by a common field: OrgID

    The table is on the one side of the relationship and project on the many side of the relationship.

     

    Now you can create a calculated column in your project table like so:

     

    Prospect =
IF( NOT( ISBLANK( 'Project'[ProjectNumber ) )
, "Yes"
, "No"
)

     

     
    From my impression, the right outer join does not matter for the DAX calculated column. Basically there are tables, basically there are no views. Tables are related by ONE column.

     

    Hopefully, this helps to tackle your challenge.

     

    Regards,

    Tom

  • lbudack's avatar
    lbudack
    Advocate III
    5 years ago

    Thanks, TomMartens! I ended up getting a solution that doesn't involve doing a join or referencing a separate table. Your expression works, but it doesn't quite get me the answer I need since all rows show up as "Yes". 

     

    However, in case I come across this situation again, here's more info. 

     

    I needed to mark organizations who are or are not prospects by determining if they've ever been used on a project record. 

     

    Org Table:

    OrgIDOrganizationNameProspect?
    1Company ABC 
    2Consultant ABC 
    3Company XYZ 

     

    Project Table:

    ProjectNumProjectNameOrgIDOrganizationName
    123Project A1Company ABC
    324Project B5Company JKL
    568Project C2Consultant ABC

     

    • Icey's avatar
      Icey
      Community Support
      5 years ago

      Hi lbudack ,

       

      Create a calculated column like so:

      Prospect? = 
IF (
    SUMX (
        'Project Table',
        FIND ( 'Org Table'[OrgID], 'Project Table'[OrgID],, 0 )
    ) > 0,
    1
)

       

       

      Best regards

      Icey

       

      If this post helps,then consider Accepting it as the solution to help other members find it faster.