DAX measure using text column

[airranz]

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]

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]

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]

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

 

[airranz]

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]

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]

Genius!!!

Exactly what i need