Forum Discussion

myou's avatar
myou
Helper II
7 years ago
Solved

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 ...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

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