Check if value is in a filtered list DAX

[PowerbiGuest23]

I am tring to create a measure determining if an attribute is the same as that of an item selected by a slicer. Using the example below, the column "Is Selected" returns 1 if a Team was chosen using a slicer. I am trying to create the column "Same Color" that returns 1 if the Uniform Color is the same as the team where "Is Selected" is 1.

In other words, here is the logic I am trying to implement in DAX: if UniformColor is in list of UniformColors of the "Is Selected" team, return 1.

Team	Uniform Color	Is Selected	Same Color
A	Red	1	1
A	White	1	1
B	Green	0	0
B	Red	0	1
C	Blue	0	0
C	White	0	1

I have a DAX query that I feel is close, but it only displays results for the selected team and is blank for every other team.

RelevantUnit =
var TableVar = DISTINCT(SELECTCOLUMNS(FILTER('Team',[Is Selected] = 1),"SimilarColor",'Team'[Uniform Color]))
return CALCULATE(COUNTROWS('Team'),'Team'[Uniform Color] in TableVar)

[amitchandak · Accepted solution]

@PowerbiGuest23 , First of all, a column can not use selectedvalue or the slicer value. So you need to create a measure

You need an independent team table for slicer

Measure  =

var _sel = summarize(Filter(Table, Table[Team] = selectedvalue(Team[Team]) ), Team[Color])

return

countrows(filter(Table, Table[color] in _sel))

[v-easonf-msft · Accepted solution]

Hi, @PowerbiGuest23 

Please try formulas as below:

Is Selected = 
var a=IF(SELECTEDVALUE(Team[Team]) in VALUES('Slicer Team'[Team]),1,0)
return IF(ISFILTERED('Slicer Team'[Team]),a,0)

Same color = 
var tab=SUMMARIZE(Filter(all(Team), Team[Team] in values('Slicer Team'[Team]) ), Team[Uniform Color])
var b=IF(SELECTEDVALUE(Team[Uniform Color]) in tab,1,0)
return IF(ISFILTERED('Slicer Team'[Team]),b,0)

Best Regards,
Community Support Team _ Eason

[amitchandak · Accepted solution]

@PowerbiGuest23 , First of all, a column can not use selectedvalue or the slicer value. So you need to create a measure

You need an independent team table for slicer

Measure  =

var _sel = summarize(Filter(Table, Table[Team] = selectedvalue(Team[Team]) ), Team[Color])

return

countrows(filter(Table, Table[color] in _sel))