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_salesfor...
DAX
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.