Implementing Multiple condition using DAX Expression

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

Answer 1

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/

Answer 2

An in-memory table containing three values.

Thanks

---

BI Developer

Answer 3

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/

Answer 4

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

Answer 5

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/

Answer 6

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

Answer 7

=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