# DAX measure using text column



## airranz (Jun 10, 2014)

Hello I am using power pivto in Excel 2010 in order to create some reports, the problem is that in my reports i need not only numeric columns but text. I know DAX measures can return text, but in this case the problem is that the text column should be used as input for the measure. My data are like this

Country Year Value Flag
UK         2008  2      a
UK         2009  10     b
UK         2010  7      b
UK         2011  8      p
US         2008  2       
US         2009  11     
US         2010  5      a
US         2011  3      p

My report should look like:

Country  2008   2009  2010 2011
UK           2 a   10 b    7 b     8 p
US           2      11      5 a     3 p

Obvously I can do it without the flags easily, I could replace the flags by numeric values and at the end transform the numbers in the correspondant flag, but that requires processing in every single report I have to do.
I can do it also with CUBE functions, but again not ideal.
The ideal would be to have a measure that could return the flag associated to the value
Is that possible


----------



## scottsen (Jun 10, 2014)

So, you have country on rows, and year on column, and... what do you want the aggregate value to be for those?   If you know its always just 1 row per year/country maybe just   VALUES(myTable[myValue]) & " " VALUES(myTable[myFlag]) ?


----------



## airranz (Jun 11, 2014)

I have tried that and similar things but this is the error message that I receive:
"Calculation error in measure 'myTable'[Measure 1]: The value for 'value_n' cannot be determined. Either 'value_n' doesn't exist, or there is no current row for a column named 'value_n'."


----------



## scottsen (Jun 11, 2014)

If you can share the workbook (dropbox, google drive, whatever), i'd be happy to take a look.


----------



## airranz (Jun 11, 2014)

Here you are
https://www.dropbox.com/s/mwqvpf2s1fdkpux/test_measure.xlsx

I want the data from the column flag to appear in the report


----------



## scottsen (Jun 11, 2014)

Does this do what you want?  A measure (calculated field) ...

=SUM ( ilc_di11[value_n] ) & " "
& IF (
HASONEVALUE ( ilc_di11[flag] ),
VALUES ( ilc_di11[flag] ),
"*"
)


----------



## airranz (Jun 11, 2014)

Genius!!! 

Exactly what i need


----------

