Forum Discussion

anjanikumar's avatar
anjanikumar
Icon for Helper III rankHelper III
4 years ago

Convert this SQL query to DAX measure

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

DAX

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

    • anjanikumar's avatar
      anjanikumar
      Icon for Helper III rankHelper III
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.