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

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.