Forum Discussion

DoeJoe's avatar
DoeJoe
Frequent Visitor
1 year ago
Solved

Filter table visual from a Measure on a Matrix Visual

I have a table that has multiple fields with blank values.

I create Measures to count the blank values for each field and display them in a Matrix using the Switch Values to Rows option.

I have a visualization table with all data in the same table the measures were made from

I want to click on any given measure in my Matrix to filter my table with all data only where those values are blank

 

Matrix:

Field 1  23

Field 2  3

Field 3  6

 

When selecting Field one on Matrix, Report Table should display 23 records

 

Any help?

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello DoeJoe,

     

    Can you please try this approach - You already have measures to count the blank values:

    CountBlank_Field1 = 
CALCULATE(COUNTROWS(TableName), ISBLANK(TableName[Field1]))

CountBlank_Field2 = 
CALCULATE(COUNTROWS(TableName), ISBLANK(TableName[Field2]))

CountBlank_Field3 = 
CALCULATE(COUNTROWS(TableName), ISBLANK(TableName[Field3]))

    Now, create a Selection Indicator:

    SelectedFieldFilter = 
VAR SelectedField = SELECTEDVALUE(MatrixFields[FieldName])
RETURN
    SWITCH(
        TRUE(),
        SelectedField = "Field1", ISBLANK(TableName[Field1]),
        SelectedField = "Field2", ISBLANK(TableName[Field2]),
        SelectedField = "Field3", ISBLANK(TableName[Field3]),
        TRUE()
    )

    Hope this helps!

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Sahir_Maharaj ,thanks for the quick reply, I'll add more.

    Hi DoeJoe ,

    Creating a slicer instead of clicking through measures.

    Try this

    Measure = 
VAR _column = SELECTEDVALUE('Table (2)'[Column])
RETURN
IF(ISFILTERED('Table (2)'[Column]),
SWITCH(TRUE(),
_column = "Column1",IF( SELECTEDVALUE('Table'[Column1]) = "",SELECTEDVALUE('Table'[Column1])),
_column = "Column2",IF( SELECTEDVALUE('Table'[Column2]) = "",SELECTEDVALUE('Table'[Column2])),
_column = "Column3",IF( SELECTEDVALUE('Table'[Column3]) = "",SELECTEDVALUE('Table'[Column3]))
),""
)

    Best Regards

     

5 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    Hello DoeJoe,

     

    Can you please try this approach - You already have measures to count the blank values:

    CountBlank_Field1 = 
CALCULATE(COUNTROWS(TableName), ISBLANK(TableName[Field1]))

CountBlank_Field2 = 
CALCULATE(COUNTROWS(TableName), ISBLANK(TableName[Field2]))

CountBlank_Field3 = 
CALCULATE(COUNTROWS(TableName), ISBLANK(TableName[Field3]))

    Now, create a Selection Indicator:

    SelectedFieldFilter = 
VAR SelectedField = SELECTEDVALUE(MatrixFields[FieldName])
RETURN
    SWITCH(
        TRUE(),
        SelectedField = "Field1", ISBLANK(TableName[Field1]),
        SelectedField = "Field2", ISBLANK(TableName[Field2]),
        SelectedField = "Field3", ISBLANK(TableName[Field3]),
        TRUE()
    )

    Hope this helps!

    • DoeJoe's avatar
      DoeJoe
      Frequent Visitor
      1 year ago

      Ok, my fields in the table are actually Empty not Blank and when I use your formula I cannot get it to work with ISEMPTY. Is there a tweak?

       

      Once I have the Measures setup right, I am not sure where/how to create/apply the Selection Filter.

       

      To explain better - I have one table, let's call it Data

      I created six measures to identify how many are empty:

      COUNTROWS(
              FILTER(
                  'Data',
                   ([Field 1]=""
              )
          ))

       

      I create a Matrix visualization using the measures from this table. Let's call the visualization "Blank Records."

       

      I created a second visualization using the actual data fields from the same table, Data. Let's Call this Visualization "Errors".

       

      Not sure how to proceed with Selected Field Filter? Is this another measure that gets added to the Errors visualization filter?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Sahir_Maharaj ,thanks for the quick reply, I'll add more.

        Hi DoeJoe ,

        Creating a slicer instead of clicking through measures.

        Try this

        Measure = 
VAR _column = SELECTEDVALUE('Table (2)'[Column])
RETURN
IF(ISFILTERED('Table (2)'[Column]),
SWITCH(TRUE(),
_column = "Column1",IF( SELECTEDVALUE('Table'[Column1]) = "",SELECTEDVALUE('Table'[Column1])),
_column = "Column2",IF( SELECTEDVALUE('Table'[Column2]) = "",SELECTEDVALUE('Table'[Column2])),
_column = "Column3",IF( SELECTEDVALUE('Table'[Column3]) = "",SELECTEDVALUE('Table'[Column3]))
),""
)

        Best Regards

         

    • DoeJoe's avatar
      DoeJoe
      Frequent Visitor
      1 year ago

      Super helpful, thank you. My requirements changed slightly in that the filtering key is in separate tables.

      Table 1 = All data including Unique Key

      Table 2 = Issue 1 Unique Key

      Table 3 = Issue 2 Unique Key

       

      I have a table visualization of fields in Table 1 that need to be filtered by all Unique Keys.

       

       

  • DoeJoe's avatar
    DoeJoe
    Frequent Visitor
    1 year ago

    You guys are awesome, thank you so much. 


    Clearly, I am very new to this and I was able to use the CountRows function within my master table to achieve the same thing I was creating complex code around multiple tables.