Using measure as advanced filter

[Drake1002]

I have a column 'a' with delimiter comma in a table A. I need to choose data based on the single value selected from the column with delimiter.

My approach is creating a B list/ table with distinct values from the column 'a' and then making a measure as

Selected=SELECTEDVALUE(B[b])

Now adding this measure to a table visual does not work in advanced filtering 

[Drake1002]

Table A 

Column a

A,B,X

A,V,B

B,C,G

B,H,R

M,A,C

Z,C,B

Table B

Column b

A

B

C

X

V

G

H

R

M

Z

If i select A in the slicer then output visual be

A,B,X

A,B,V

M,A,C

[Anonymous]

Hi @Drake1002 

Please try this:

First of all, I add a measure:

MEASURE =
VAR _Slicer =
    SELECTEDVALUE ( 'Table B'[Column b] )
RETURN
    IF (
        FIND ( _Slicer, SELECTEDVALUE ( 'Table A'[Column a] ),, BLANK () ) <> BLANK (),
        _Slicer
    )

 Then create a table visual like this:

The result is as follow:

Best Regards

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

[Drake1002]

Thanks Zhengdong Xu. What if i want to select A and B both and it should display values having either A or B?

[Anonymous · Accepted solution]

Hi @Drake1002 

Try this measure which can choose up to 3 values:

Measure 2 = 
	VAR _slicer = CONCATENATEX(
		'Table B',
		'Table B'[Column b]
	)
	RETURN
		IF(
			COUNT('Table B'[Column b]) = 1 && FIND(
				MID(
					_slicer,
					1,
					1
				),
				SELECTEDVALUE('Table A'[Column a]),
				,
				BLANK()
			) <> BLANK(),
			_slicer,
			IF(
				COUNT('Table B'[Column b]) = 2 && FIND(
					MID(
						_slicer,
						1,
						1
					),
					SELECTEDVALUE('Table A'[Column a]),
					,
					BLANK()
				) <> BLANK() && FIND(
					MID(
						_slicer,
						2,
						1
					),
					SELECTEDVALUE('Table A'[Column a]),
					,
					BLANK()
				) <> BLANK(),
				_slicer,
				IF(
					COUNT('Table B'[Column b]) = 3 && FIND(
						MID(
							_slicer,
							1,
							1
						),
						SELECTEDVALUE('Table A'[Column a]),
						,
						BLANK()
					) <> BLANK() && FIND(
						MID(
							_slicer,
							2,
							1
						),
						SELECTEDVALUE('Table A'[Column a]),
						,
						BLANK()
					) <> BLANK() && FIND(
						MID(
							_slicer,
							3,
							1
						),
						SELECTEDVALUE('Table A'[Column a]),
						,
						BLANK()
					) <> BLANK(),
					_slicer
				)
			)
		)

The result is as follow:

Best Regards

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