Forum Discussion

Hifni93's avatar
Hifni93
Frequent Visitor
1 year ago
Solved

Dybamic Filter

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...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Hifni93
Frequent Visitor
1 year ago

 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 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    Hifni93
    Frequent Visitor
    1 year ago

    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 .