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 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-msft's avatar
    v-cherch-msft
    Microsoft 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