Advanced filtering of table 'Is blank' not working

[Anonymous]

Hi there, Please can someone help me resolve the following?

I have two simple tables with four entries in the student Name table (S1-S4) and five entries in the Event table (E1-E5). These tables are Crossjoined into a new table to show all 20 possible options of Students and Events. The final table contains a Log of Students who attended Events.

I then created Columns in both the Crossjoin and Log tables of Event&Student called Concatenate.

A Table visual was used to display all 20 possible options from the Crossjoin and those listed in the Log table which shows up perfectly. If I use the Advanced Filter to choose when the Log.Concatenate field is non-blank, the correct 6 choices appear in the Table visual. However, when I choose the reverse (which is what I need to show the options that are yet to be completed) and use the Advanced Filter 'Is blank' everything disappears from the Table visual. What am I doing wrong?

Thanks in advance.....Steve

Student Events.pbix

Student events.xlsx

[Anonymous · Accepted solution]

@Anonymous  - The reason for the result is straightforward - the table contains no blank values, so filtering to only show blank values removes everything.

A solution:

1. Create a new Measure:

Find Blanks = 
var a = FILTER(
    'Cross Join Table',
    COUNTROWS(RELATEDTABLE('Log')) = 0
)
return COUNTROWS(a)

2. You can use this measure in your table to limit rows, but you'll need to remove the column from log table, and also don't "Show Items with No Data"

Hope this helps,

Nathan

[Anonymous]

That is brilliant, thank you ever so much.

Steve