Forum Discussion
myou
7 years agoHelper 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)).
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
1 Reply
- v-cherch-msftMicrosoft 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