Logical Measure Using 3 Tables

[andybamber]

Hello Community!

I'm not sure this is even possible... So I have 3 tables... a user table which has a relationship to an organisational hierarchy table using the user_id, the organisation table in turn has a relationship to a cost centre table using an obs_ref key. There isnt any key to link the user table direct to the organisational table.

What I would like to be able to do is create a measure which looks at the user in the user table, and if that user's cost centre has specific characters in the string it will create a value "xxx, xx"... essentially its masking the users name depending which cost centre they are in...

I could do some merging in power query and then add a custom column etc. but I was wondering if dax could handle this in a measure?

Cheers

Andy

[amitchandak]

@andybamber ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[andybamber]

Hi There! just wondering if you managed to find a solutiuon for this?

Cheers

Andy

[andybamber]

Hi There! thanks for the quick response... i've attached an Excel which hopefully illustrates my issue

User table joined to Org on User_Id, and then Org joined to CC on OBS_Key... i need to look at the user_id (this table also contains full name) and if the CC contains DE then the measure needs to show something like "xx, xx", else actual name "Bamber, A"

Cheers

Andy