none
DAX Beginner...Help on Conditional Statements

    Question

  • I am a newbie to DAX queries. I need suggestions in re writing MDX Case statements in DAX format. I can use IF conditions but there are CASE and ELSE mdx statements which I am not sure how I should write in DAX format. Can anybody give me a hint.

    For example I have the following case statement:

    CASE

                    WHEN(([Measures].[Daily Rolling Run Rate] * 10 ) + [Measures].[Marketing Campaigns By BG] + [Measures].[ESD @ Retail Channel Monthly - Hidden])<100

                    THEN100

                    ELSE(([Measures].[Daily Rolling Run Rate] * 10 ) + [Measures].[Marketing Campaigns By BG] + [Measures].[ESD @ Retail Channel Monthly - Hidden])

                END

    In order to convert the above, should I use IF? When should I use IIF and ELSE in DAX.

    Thank you,

    Sandeep 

    Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

    Thursday, February 16, 2012 5:00 PM

Answers

  • There's a SWITCH() function you can use in DAX that does much the same thing as a CASE statement:

    http://technet.microsoft.com/en-us/library/gg492166(v=sql.110).aspx

    But it looks like, for this calculation, you could just use an IF().

    HTH,

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    • Proposed as answer by Jerry NeeModerator Monday, February 20, 2012 6:42 AM
    • Marked as answer by san463 Tuesday, March 06, 2012 9:44 PM
    Thursday, February 16, 2012 10:13 PM
    Moderator
  • Thank you Chris.. Your suggestion is appreciated. Could you also please let me know what are the DAX equivalent functions of following MDX functions. I tried in the DAX reference but in vain...These are the MDX functions....LASTPERIODS,LinkMember,ClosingPeriod,STRTOMEMBER,ascendants


    Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

    • Marked as answer by san463 Tuesday, March 06, 2012 9:44 PM
    Monday, February 20, 2012 6:26 PM
  • There are no equivalents to LinkMember, StrToMember or Ascendants. For LastPeriods and ClosingPeriod check out functions like DatesBetween and LastDate here: http://technet.microsoft.com/en-us/library/ee634763.aspx.

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    • Marked as answer by san463 Tuesday, March 06, 2012 9:44 PM
    Monday, February 20, 2012 9:55 PM
    Moderator

All replies

  • There's a SWITCH() function you can use in DAX that does much the same thing as a CASE statement:

    http://technet.microsoft.com/en-us/library/gg492166(v=sql.110).aspx

    But it looks like, for this calculation, you could just use an IF().

    HTH,

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    • Proposed as answer by Jerry NeeModerator Monday, February 20, 2012 6:42 AM
    • Marked as answer by san463 Tuesday, March 06, 2012 9:44 PM
    Thursday, February 16, 2012 10:13 PM
    Moderator
  • Thank you Chris.. Your suggestion is appreciated. Could you also please let me know what are the DAX equivalent functions of following MDX functions. I tried in the DAX reference but in vain...These are the MDX functions....LASTPERIODS,LinkMember,ClosingPeriod,STRTOMEMBER,ascendants


    Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

    • Marked as answer by san463 Tuesday, March 06, 2012 9:44 PM
    Monday, February 20, 2012 6:26 PM
  • There are no equivalents to LinkMember, StrToMember or Ascendants. For LastPeriods and ClosingPeriod check out functions like DatesBetween and LastDate here: http://technet.microsoft.com/en-us/library/ee634763.aspx.

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    • Marked as answer by san463 Tuesday, March 06, 2012 9:44 PM
    Monday, February 20, 2012 9:55 PM
    Moderator
  •                I am trying to convert the following MDX query into DAX.

    CREATE MEMBER CURRENTCUBE.[Measures].[30 Day Issues From Store]

    AS CASE WHEN ISEmpty(SUM( LASTPERIODS(30,LinkMember(ClosingPeriod([Fulfillment Date].[Calendar].[Full Date]),[Fulfillment Date].[Calendar])) , [Measures].[Total Quantity Issued From Store] ))

         THENSUM( LASTPERIODS(30,STRTOMEMBER("[Fulfillment Date].[Calendar].[Full Date].&["+ format(now(),"yyyy-MM-dd")+"T00:00:00]")) , [Measures].[Total Quantity Issued From Store] )

         ELSESUM( LASTPERIODS(30,LinkMember(ClosingPeriod([Fulfillment Date].[Calendar].[Full Date]),[Fulfillment Date].[Calendar])) , [Measures].[Total Quantity Issued From Store] )

    END

               So far I was only able to get to the following and I am not sure this is correct. I request you to  provide me any pointers which might help me get this query.

    Test30: =calculate (SUMX(‘FactStoreActivity’[Total Quantity Issued From Store],DATESMTD(‘Fulfillment Date’[Full Date]))

    Sandeep


    Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

    Tuesday, March 06, 2012 9:44 PM
  • The DatesMTD function isn't the one you want here - in your MDX you're asking for the last 30 days which means you should be using the DatesBetween() function in DAX - http://technet.microsoft.com/en-us/library/ee634557.aspx.

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    Tuesday, March 06, 2012 9:50 PM
    Moderator
  • Thank you Chris. Based upon your input i re worked on the formula. But I am unable to get the 30 days period in the dates between since it asks for a start date. I go the end of date with last date function. Please suggest any modifications.

    =CALCULATE(SUM(‘FactStoreActivity’[Total Quantity Issued From Store]), DATESBETWEEN((‘Fulfillment Date’[Full Date]),DATE(2003,6,1),LastDate(‘Fulfillment Date’[Full Date]))) 


    Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

    Tuesday, March 06, 2012 10:08 PM
  • Actually, thinking about it the DatesInPeriod() function is what you need: http://technet.microsoft.com/en-us/library/ee634539.aspx. You just need the starting point (which is returned by the LastDate() function in your example) and then go 30 days back.

    Chris


    Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

    Tuesday, March 06, 2012 10:25 PM
    Moderator