Forum Discussion

Drake1002's avatar
Drake1002
Frequent Visitor
2 years ago
Solved

Using measure as advanced filter

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 distin...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Drake1002's avatar
Drake1002
Frequent Visitor
2 years ago

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

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's avatar
    Drake1002
    Frequent Visitor
    2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.