Manipulating column on USERPRINCIPALNAME()

[MiKeZZa]

Hi all,

I want to make something like this:

TABLE:

Field 1

Field 2

Field 3

This table must have a Field 4 which contains Field 1 if the logged in person is in Active Directory Group 'Group A', if he is 'Group B' it must contain Field 2 and otherwise it must contain Field 3.

I have a table in my model which contains this:

UPN                                         IsMemberOfGroupA     IsMemberOfGroupB

mailaddress                             1                                    0
.....................

I've tried so many things, but I'm not able to fix this. I get problems with using USERPRINCIPALNAME() in row-context, not unique values and so on.... I'm totally clueless.

Can somebody tell me with which DAX expression in TABLE I can do this?

[vanessafvg]

have you tried the switch statement?

something to the effect off

switch(true(),

          and (ismembergroupA = 1, ismembergroupb = 0), "Result",

      and (ismembergroupA = 0, ismembergroupb = 1), "Result",

      "Other Result")

[MiKeZZa]

That gives an error that I've already got and what makes clear that I'm not familiar enough with DAX... It gives:

A single value for column 'IsGroupA' in table 'member groups' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[vanessafvg]

can i see your dax code with an example of the data

[MiKeZZa]

We've mailed and Vanessas solution is to combine everything in 1 table. A solution that absolute will work, but not what I'm looking for...

I'm afraid I'm looking for something that is not possible....

[vanessafvg]

out of curiosity why does combining the tables not work for you?

[MiKeZZa]

I think it will work, but it's not what I was hoping for. This gives me 2 'facttables', only for something easy (in other languages/tools) as this:

if (username IN (list of people that are manager)
{
           FieldA
}
else
{
           FieldB
}

So I'm still hoping to solve it another way, that is also easier to understand for other people at a later moment....