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 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 

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.

4 Replies

  • 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?