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.  ...
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.

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.