How to write this SQL to DAX

[myou]

I am new in using Power Bi

I want to know how can I convert this SQL to DAX

SELECT DATENAME( MONTH,(createdon)) AS [Date],
              Sum(CASE 
                    WHEN statecode = 1 THEN 1 
                    ELSE 0 
                  END) AS [#OppWon], 
              Sum(CASE 
                    WHEN statecode = 2 THEN 1 
                    ELSE 0 
                  END) AS [#OppLost], 
              Sum(CASE 
                    WHEN statecode != 0 THEN 1 
                    ELSE 0 
                  END) AS [#TotalOpps],

			 [winrate]= ([oppwon]/[#totalopps])*100
		      		   
       FROM   mytable 
       GROUP BY  DATENAME( MONTH,(createdon))

.

[v-cherch-msft · Accepted solution]

Hi @myou

You may refer to below dax to create a table.

Table =
SUMMARIZE (
    mytable,
    mytable[createdon].[Month],
    "OppWon", SUMX ( mytable, IF ( mytable[statecode] = 1, 1, 0 ) ),
    "OppLost", SUMX ( mytable, IF ( mytable[statecode] = 2, 1, 0 ) ),
    "TotalOpps", SUMX ( mytable, IF ( mytable[statecode] <> 0, 1, 0 ) ),
    "winrate", SUM ( mytable[OppWon] ) / SUM ( mytable[TotalOpps] )
        * 100
)

Regards,

Cherie