locked
Implementing Multiple condition using DAX Expression RRS feed

  • Question

  • =IF(CR_PD[Year]= "2011", "201010"|| "201101"||  "201105")

    Please I need a dax expression to return 201010, 201101 and 201105 if year is equal to 2011. This expression work fine for only one value. Does anyone know how to tweek this to return multiple values?

    Thanks

    


    BI Developer

    Thursday, March 8, 2012 7:56 PM

Answers

  • =if([FY] = "2008" && [CR] = "200710", "200710", IF( [FY] = "2008" && [CR] = "200801", "200801", IF([FY] = "2008" && [CR] = "200805", "200805")))

    This expression worked. It map three values of CR to one value of FY. In a one to many relationship. Thanks 

    BI Developer

    • Marked as answer by Abioye Friday, March 9, 2012 7:39 PM
    Friday, March 9, 2012 7:39 PM

All replies

  • Abioye,

    What exactly are you expecting as a result?  A concatenated string? or an in-memory table containing the three values?




    Javier Guillen
    http://javierguillen.wordpress.com/

    Thursday, March 8, 2012 9:02 PM
    Answerer
  • An in-memory table containing three values.

    Thanks


    BI Developer

    Friday, March 9, 2012 1:24 PM
  • You can easily do this if a table already exists in your PowerPivot data model containing the three values you mention.  In this case, you would use an calculated column expression like:

    =IF(CR_PD[year] = "2011",

    COUNTROWS(FILTER(Table1, Table1[values] = "201010" || Table1[values] = "201101" || Table1[values] = "201105")),BLANK())


    Note: i added the COUNTROW functions to test that all rows with expected values are returned.



    Javier Guillen
    http://javierguillen.wordpress.com/

    Friday, March 9, 2012 2:05 PM
    Answerer
  • Thanks for your quick response, I got error "too many arguments were passed to 'COUNTROW' Function. I am new to DAX expression and this is what I want to do. I have a column in Powerpivot from data source called FY (2008, 2009, 2010, 2011, 2012). I want to map each of the value of FY such as 2008 to '200710', 20801, 200805" to a calculated column CR. Like table below
    FY         CR
    2008     200710
    2008     200801
    2008     200805
    

    Please how can I accomplish this using DAX expression?

    Thanks


    BI Developer

    Friday, March 9, 2012 3:16 PM
  • Do you have another table in your PowerPivot model with the lookup values?  if so, what are the common keys?

    If the translation is fairly simple, it may be a good idea to implement it on the query to the source. 




    Javier Guillen
    http://javierguillen.wordpress.com/

    Friday, March 9, 2012 3:34 PM
    Answerer
  • There is another table CalenderRound with such values '200710', 200801, 200805 map to FY 2008. But this  can somethimes have more than three values to FY values such as 2008 and 2009. So I want to have a calcuted column with only three value such as 200710,200801,200805 map to 2008 of FY. FY is a foreign key in CalenderRound.

    Thanks


    BI Developer

    Friday, March 9, 2012 3:53 PM
  • =if([FY] = "2008" && [CR] = "200710", "200710", IF( [FY] = "2008" && [CR] = "200801", "200801", IF([FY] = "2008" && [CR] = "200805", "200805")))

    This expression worked. It map three values of CR to one value of FY. In a one to many relationship. Thanks 

    BI Developer

    • Marked as answer by Abioye Friday, March 9, 2012 7:39 PM
    Friday, March 9, 2012 7:39 PM