Filter table visual from a Measure on a Matrix Visual

[DoeJoe]

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?

[Sahir_Maharaj · Accepted solution]

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]

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.

[Sahir_Maharaj · Accepted solution]

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]

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]

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 · Accepted solution]

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