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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
DoeJoe
Frequent Visitor

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?

2 ACCEPTED SOLUTIONS
Sahir_Maharaj
Super User
Super User

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!


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

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]))
),""
)

vzhouwenmsft_0-1734427291138.png

vzhouwenmsft_1-1734427328019.png

Best Regards

 

View solution in original post

5 REPLIES 5
DoeJoe
Frequent Visitor

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
Super User
Super User

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!


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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.

 

 

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?

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]))
),""
)

vzhouwenmsft_0-1734427291138.png

vzhouwenmsft_1-1734427328019.png

Best Regards

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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