Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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?
Solved! Go to 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!
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
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.
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!
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:
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]))
),""
)
Best Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
125 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |