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.

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