Multicolumn Slicer for Table Filtering

MackAttack26 — Wed, 03 Jul 2024 18:59:03 GMT

I have the following set of data. The data table as a column that has concatenated all of the column names that have a TRUE value for the record.

Data Table

data_col	tf_col_1	tf_col_2	tf_col_3	tf_col_4	tf_col_concat
Column Data 1	TRUE	TRUE	TRUE	TRUE	tf_col_1,tf_col_2,tf_col_3,tf_col_4
Column Data 2	FALSE	TRUE	NULL	TRUE	tf_col_2,tf_col_4
Column Data 3	FALSE	FALSE	FALSE	TRUE	tf_col_4
Column Data 4	TRUE	TRUE	TRUE	FALSE	tf_col_1,tf_col_2,tf_col_3

I have created a parameter based on the "TF Col"s in the table and the code behind it looks like this

TF_COL_PARAM = {

("TF Col 1", NAMEOF('Query1'[tf_col_1]), 0),

("TF Col 2", NAMEOF('Query1'[tf_col_2]), 1),

("TF Col 3", NAMEOF('Query1'[tf_col_3]), 2),

("TF Col 4", NAMEOF('Query1'[tf_col_4]), 3),

}

TF COL PARAM	TF COL PARAM Fields	TF COL PARAM Order
TF Col 1	'Query1'[tf_col_1]	0
TF Col 2	'Query1'[tf_col_2]	1
TF Col 3	'Query1'[tf_col_3]	2
TF Col 4	'Query1'[tf_col_4]	3

I have created a checkbox slicer based on the TF_COL_PARAM parameter with the following options:

TF Options

TF Col 1

TF Col 2

TF Col 3

TF Col 4

I would like the table to filter based on the selections in the slicer. If the selection in the slicer is selected, show me all the records in the table that have a corresponding TRUE value. If the solution could also work for Multiple selections in the slicer, that would be great. I do not want to create an individual slicer for each of the tf_col columns as there could be N number of columns and that would not be practical. Our users are not going to use filters, and a combined slicer, such as what you would see in "TF Options" is what we would want.

The problem I am having is the slicer only returns the value from the slicer for "TF COL PARAM" or "TF COL PARAM Fields" and not the column name just the column name. I believe I need to create a DAX query along the lines of the following, but this DAX is invalid:

Matches Values = IF (CONTAINSSTRING('Query1'[tf_col_concat], SELECTEDVALUE('TF Options'[TF COL PARAM]), TRUE, FALSE)

Also, this is not the only set of data in the database that is setup like this. I have 10 other column combinations I'll be doing this for, so fixing this will help with 10 other reports I am trying to create.

Any advice on how to accomplish this? Thanks in advance for any advice and any help.

Re: Multicolumn Slicer for Table Filtering

Anonymous — Wed, 03 Jul 2024 19:44:25 GMT

nice

Re: Multicolumn Slicer for Table Filtering

Anonymous — Thu, 04 Jul 2024 11:38:37 GMT

I have one way out.

First replace the value false with null and change the columns type to true/false.

Create separate dax for each tf column

Make parameter with those dax

In the slicer you need to select parameter as well as in the table you need to select parameter
and you will get your desired "True" rows

If this meets your requirement then pls accept the same as the solution.

topic Re: Multicolumn Slicer for Table Filtering in DAX Commands and Tips

Multicolumn Slicer for Table Filtering

Re: Multicolumn Slicer for Table Filtering

Re: Multicolumn Slicer for Table Filtering