cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## How to write this SQL to DAX

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))```

.

1 ACCEPTED SOLUTION
Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.