Slicer selection value in calculated column.

[Anonymous]

Hi,

I have a scenario wherein, I have a UserName table that contains distinct user names -

UserName

Annie

Scott

Henry

Now, There is a fact table UserDetails which has following columns.

UserName	Portfolio	Country
Annie	DS	Denmark
Annie	DS	Norway
Annie	TECH	Denmark
Annie	Digital	Norway
Scott	Digital	Canada
Scott	Consulting	US
Henry	Consulting	AUSTRIA
Henry	TECH	ITALY
Henry	DS	GREECE

I need to create a matrix with Portfolio as rows and Country as column. 

when a UserName(from UserName table) is selected in a slicer it should not filter the rows but only the columns.

I want to create a dynamic column that will give me the country names for the user selected from the UserName table. 

Also, UserName and UserDetails tables are not related and I'm using reading UserName using selectedValue function and passing that measure in a column(CountryList) to fetch country names from UserDetails table.

Measure in UserName table - 

SelectedUserName = 

var selection = SELECTEDVALUE(UserName[UserName])

return selection

column in UserDetails -

CountryList = If(UserDetails[UserName]=[SelectedUserName],UserDetails[Country],Blank())

But this doesn't seem to be working. Can anyone please help?

[v-qiuyu-msft]

Hi @Anonymous, 

It's not supported to create a calculated column based on the slicer selection. You can create a measure, and turn on Show items with no data for matrix Rows bucket.  

Measure = IF(MAX('UserDetails'[UserName])=SELECTEDVALUE('UserName'[UserName]),1,BLANK())

Best Regards,
Qiuyun Yu

[Anonymous]

Hi, v-qiuyu-msft 

Also, I have 2 row levels in a matrix. 1st level - market segment and 2nd level - portfolio. 

with one column - Country.

This logic works fine if I use Name column as well as 1st level in the matrix. Not sure why it is not working otherwise.

my requirement is not to see Name in the matrix. 

Can u help with this?

[v-qiuyu-msft]

Hi @Anonymous, 

Please share pbix file with some dummy data, and clarify corresponding result. 

Best Regards,
Qiuyun Yu

[Anonymous]

Hi v-qiuyu-msft,

I have a large dataset but this measure is not returning 1 for few rows even when the names are matching from UserName and UserDetails.

Any idea why is this happening?