Dybamic Filter

[Hifni93]

Hi in power bi i have a table with column id and other 6  (1 or blank values ) columns i need to create a slicerthat have all the six columns inside so when i choose a number of columns i need to see the lines in which at least one of these coulmns is 1 while all the other unselected columns to be blank , and when I don’t choose any columns I should see the original matrix with all the combinations possible thus filtering only when choosing some columns from the slicer.
currently i am using 6 slicers to achieve this goal for example on the attached screen shots i've set 4 columns out of the 6 coulmns to empty while the other two columns (SEDE and Installdate in the ex) with all options so now i can see all the lines for these two columns with the different combinations of empty and 1 .

my idea was to have a single slicer with the six columns inside and for example to achieve the attached result i just have to falg the two columns from this slicer . any suggestions?

MY TABLE FARMSNAP

MY COLUMNS (OS_X,DOMINIO_X,INSTALLDATE_X,SEDE_X,LOGICCPU_X,CORE_X)

[v-yohua-msft · Accepted solution]

Hi, @Hifni93 

Based on your information, I create a sample table:

Create a new empty query in Power Query and enter the following as a list of column names:

= {"OS_X","DOMINIO_X","INSTALLDATE_X","SEDE_X","LOGICCPU_X","CORE_X"}

Convert this list to a table, click To Table. You can change the column name and table name:

Select Close&Apply. Then create a new measure:

Filter Logic = 
VAR Selected = ALLSELECTED('Dimension Table'[Type])
VAR Logic = 
IF(
    COUNTROWS(Selected) > 0,
    // At least one of the selected columns is 1, and all unselected columns are empty
    (
        ( MAX('Table'[OS_X]) = 1 && "OS_X" IN Selected ) ||
    ( MAX('Table'[DOMINIO_X]) = 1 && "DOMINIO_X" IN Selected ) ||
    ( MAX('Table'[INSTALLDATE_X]) = 1 && "INSTALLDATE_X" IN Selected) ||
    ( MAX('Table'[LOGICCPU_X]) = 1 && "LOGICCPU_X" IN Selected) ||
    ( MAX('Table'[CORE_X]) = 1 && "CORE_X" IN Selected) ||
    ( MAX('Table'[SEDE_X]) = 1 && "SEDE_X" IN Selected)
    )
     &&
    (
        ( MAX('Table'[OS_X]) = BLANK() || "OS_X" IN Selected ) &&
    ( MAX('Table'[DOMINIO_X]) = BLANK() || "DOMINIO_X" IN Selected ) &&
    ( MAX('Table'[INSTALLDATE_X]) = BLANK() || "INSTALLDATE_X" IN Selected) &&
    ( MAX('Table'[LOGICCPU_X]) = BLANK() || "LOGICCPU_X" IN Selected) &&
    ( MAX('Table'[CORE_X]) = BLANK() || "CORE_X" IN Selected) &&
    ( MAX('Table'[SEDE_X]) = BLANK() || "SEDE_X" IN Selected)
    )
    ,
    TRUE()
)
RETURN
IF(Logic = FALSE(), BLANK(), "True")

Create a slicer that uses the [Type] field in the newly created dimension table as a slicer, allowing multiple selections. Use the above measures as filter criteria for the table/matrix. When a slicer is selected, only rows with 1 in the selected column and empty columns are displayed. All data is displayed when no slicer is selected. Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hifni93]

 Well your solution worked fine for the desired matrix but now i am trying to build another matrix with the same filter this time I want to filter the by the sum of a row now i am using the 6 slicers to achieve this result can you help with that ? Look at the screen for the desired result 

[v-yohua-msft]

Hi, @Hifni93 

Regarding calculating sums, I suggest that you can consider duplicating the Table in Power Query and then reverse pivot the columns

Create another measure to calculate the total

TypeSum = 
VAR selected = ALLSELECTED('Dimension Table'[Type])
RETURN
CALCULATE(SUM('Table (2)'[Value]), FILTER('Table (2)', 'Table (2)'[Attribute] IN selected))

Put measure in matrix, here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hifni93]

well for the second matrix what i was trying to do is to see the for example for each (sum of attributes (1,2,3,4,5,6) since we have 6 attributes the max sum we can have is 6 for single line)  how many lines for each attribute for example when chosing two columns i am expecting each row to have a sum either 1 or 2 , 1 reperesents the lines where only one of the two columns had a value and 2 when both had a value so i wanted on the matrix to have 1 on the first line and 2 on second line and under each of the chosen columns to see the number of lines for case when only column was poplated and the case when both were poplated and it should be equal in this case . 

[amitchandak]

@Hifni93 , You can have a disconnected table with 1 and 0 value. And then based on value selected you can pass and filter various column.

Countrows(filter(Table, Table[Column1] in values(dim[Values) || Table[Column2] in values(dim[Values)  ))

One more option is field paramaters in slicer values

Power BI- Create dynamic slicers using field parameters: https://youtu.be/H2ZxWaHAJZQ
https://medium.com/@amitchandak/power-bi-power-bi-create-dynamic-slicers-using-field-parameters-2025...