DAX

[Anonymous]

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

[Anonymous]

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/

[JustJan]

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