Slicer categories based on the USERNAME from separate table

[m4xon]

Hello,

I have a table with lot of data divided into categories like (Internal, External, All) and I have a slicer to filter it easily based on these categories. I was asked to add following feature:

If USERNAME will exist in the table of users having access to the external category (dimUsersExternal) then on the Slicer show External category, otherwise others except this one.

Is it possible to achive it using DAX measure?

Thank you in advance,

Max

[m4xon]

@v-xinruzhu-msft works like a charm! Thank you!

[m4xon]

Anyone could help me with that? Thank you in advance

[v-xinruzhu-msft]

Hi @m4xon 

You can refer to the following example

Sample data 

Slicer table

Sample username measure

Username = "test1@abc.com"

Then  create a measure

Measure = var a=COUNTROWS(FILTER('User',[Column1]=[Username]))
return 
IF(a>0,IF("external" in VALUES('Table 2'[Value]),1),IF(not  "external" in VALUES('Table 2'[Value]) ,1, 0))

Then put the measure to slicer visual filter

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[m4xon]

@v-xinruzhu-msft one remark, how to modify measure to see all of the options when you are on the list. So in your example, when test1@abc.com is on the list then except All and Internal visible is also External, not only external. Thanks!

[v-xinruzhu-msft · Accepted solution]

Hi @m4xon 

You can refer to the following measure

Measure = var a=COUNTROWS(FILTER('User',[Column1]=[Username]))
return 
IF(a>0,1,IF(not  "external" in VALUES('Table 2'[Value]) ,1, 0))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.