topic Re: Convert this SQL query to DAX measure in DAX Commands and Tips

Convert this SQL query to DAX measure

anjanikumar — Thu, 20 Jan 2022 12:01:21 GMT

select opportunity_status, sum(opportunity_value) as sum_opp_value from (

select distinct opportunity_id , opportunity_status ,opportunity_value

from web_analytics_dm.dm_behaviour_campaign_salesforce dbcs

where business_unit='SEF'

and "date"='20211201') a

group by opportunity_status

Re: Convert this SQL query to DAX measure

amitchandak — Thu, 20 Jan 2022 14:05:38 GMT

anjanikumar , You can create a measure like

sumx(summarize(filter(dm_behaviour_campaign_salesforce, [business_unit]="SEF" && [date]= Date(2021,12,01)), [opportunity_id] , [opportunity_status] ,[opportunity_value]), [opportunity_value])

and use with opportunity_status is any visual

Re: Convert this SQL query to DAX measure

anjanikumar — Thu, 20 Jan 2022 14:20:37 GMT

amitchandak

Thank you so much for your response !!

want opportunity_status with Groupby .

Convert below query as DAX.

select opportunity_status, sum(opportunity_value) as sum_opp_value from (

select distinct opportunity_id , opportunity_status ,opportunity_value

from web_analytics_dm.dm_behaviour_campaign_salesforce dbcs

group by opportunity_status

Re: Convert this SQL query to DAX measure

Anonymous — Mon, 24 Jan 2022 06:59:33 GMT

Hi anjanikumar ,

You may try this measure

Measure= VAR _table = FILTER ( DISTINCT ( 'web_analytics_dm.dm_behaviour_campaign_salesforce dbcs' ), [date] = DATE ( 2021, 12, 1 ) && [business_unit] = "SEF" ) RETURN CALCULATE ( SUM ( 'web_analytics_dm.dm_behaviour_campaign_salesforce dbcs'[opportunity_value] ), FILTER ( _table, [opportunity_status] = MAX ( 'web_analytics_dm.dm_behaviour_campaign_salesforce dbcs'[opportunity_status] ) ) )

If that doesn't work, hope to provide some dummy data and expected results.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.