SQL Statement to DAX - JOIN & CASE

[lbudack]

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!  

[lbudack]

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:

OrgID	OrganizationName	Prospect?
1	Company ABC
2	Consultant ABC
3	Company XYZ

Project Table:

ProjectNum	ProjectName	OrgID	OrganizationName
123	Project A	1	Company ABC
324	Project B	5	Company JKL
568	Project C	2	Consultant ABC

[Icey · Accepted solution]

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.

[TomMartens]

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

• https://docs.microsoft.com/en-us/learn/paths/model-power-bi/

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