Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Slicer selection value in calculated column.

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.

UserNamePortfolioCountry
AnnieDSDenmark
AnnieDSNorway
AnnieTECHDenmark
AnnieDigitalNorway
ScottDigitalCanada
ScottConsultingUS
HenryConsultingAUSTRIA
HenryTECHITALY
HenryDSGREECE

 

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?

 

Reports

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-qiuyu-msft
        Community Support
        7 years ago

        Hi Anonymous, 

         

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

         

        Best Regards,
        Qiuyun Yu