Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX

Hi,

 

I want to convert this SQL query into dax for one of my calculations. I used Leftouterjoin but brings blank value. How to write the same logic in DAX.

 

Query:

select count(*) from "CB_ODS_BA_TEST"."GTM"."FRESHSALES_DEALS_ACTIVE" deal left outer join "CB_ODS_BA_TEST"."GTM"."FRESHSALES_LEADS_ACTIVE" lead on deal.deal_id=lead.deal_id where lead.deal_id is null

 

Reg,

Dhana

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Can't you sort of invert the where statement to "show me all Deals except if there is a Lead"?

     

    Then you can use a measure like this": COUNTROWS(EXCEPT( 'Deal', 'Lead'))

     

    Jan 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Please go to Model Panel (Left hand ribbon) to do the joining among tables as coded in SQL, And then use the below DAX formula to  count rows.

     

    SQL_To_DAX =
CALCULATE ( COUNTROWS ( 'Table' ), lead.deal_id = null )

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.

     

    Regards,

    Gaurav Raj Singh

    LinkedIN : https://www.linkedin.com/in/gauravrajsingh/