Merge dims on multiple rows on single row in straight table

[#1]

Hi,

I am having a bit of trouble merging multiple attributes linked to a fact table into a single row. Any help would be greatly appreciated  

Here is the data model.

There are multiple roles/people associated with every project. Some of these people are internal users and others are external users.

This is the desired o/p

For every project we want to merge the multiple roles into a single row in a straight table.

I tried playing around with  Aggr() function e.g. Aggr(MaxString({<ROLE={'Client'}>} ORG_NAME), ORG_NAME)

but it doesn't work when all the fields are added. I am definitely missing something but don't know how to proceed.

The sample app is attached. Can someone please suggest a possible approach?

Thanks in advance!

[ogster1974]

Have you tried pivoting on your data on role and bringing back your people in a separate table.  Your model supports multiple users in any given role.

Regards

Andy

[#3]

Thank Andy.

That is an option but we are building a mashup which has an export functionality; So straight table is the only option.

I did get it to work by using Only()

Only({1<PROJECT_ID=P({1<PROJECT_ID=P({$}PROJECT_ID)>}PROJECT_ID), ROLE={'Project Manager'}>} USER_NAME)

and then using he same approach for other roles. !

But then, we loose the search option because we have to add it as a measure. May be I have to duplicate the users table at least into Client Contacts & Internal users.

[marcohadiyanto]

Hi Aravind

Is that visualization that you want fixed column?

I tried using pivot to produce that you want. see attached file