Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MackAttack26
New Member

Multicolumn Slicer for Table Filtering

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_coltf_col_1tf_col_2tf_col_3tf_col_4tf_col_concat
Column Data 1TRUETRUETRUETRUEtf_col_1,tf_col_2,tf_col_3,tf_col_4
Column Data 2FALSETRUENULLTRUEtf_col_2,tf_col_4
Column Data 3FALSEFALSEFALSETRUEtf_col_4
Column Data 4TRUETRUETRUEFALSEtf_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 PARAMTF 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.

1 ACCEPTED SOLUTION
Rakesh1705
Helper I
Helper I

I have one way out.

 

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

Rakesh1705_0-1720092909417.png

Create separate dax for each tf column

Rakesh1705_1-1720092958146.png

 

Make parameter with those dax

Rakesh1705_2-1720092999373.png

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

 

Rakesh1705_3-1720093078377.png

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

View solution in original post

2 REPLIES 2
Rakesh1705
Helper I
Helper I

I have one way out.

 

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

Rakesh1705_0-1720092909417.png

Create separate dax for each tf column

Rakesh1705_1-1720092958146.png

 

Make parameter with those dax

Rakesh1705_2-1720092999373.png

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

 

Rakesh1705_3-1720093078377.png

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

manu2324_1
New Member

nice

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.