How to filter data for no date rows

[BI_Analyticz]

I have a data like below. Name, Date, Submission State.

If you look at the below data John has no data for 3 and 4 Mar 2021. I managed to display the empty rows visually using the option "Show items with no data".

But I want to quickly filter the visual to see the list of names that dont have data for any of the dates during a specific period. if I use a drop down filter using the Submisson State I get YES / NO as values inside the drop down which is obvious. I select NO and export the data and circulate it. Similarly I want to filter and export the names of those who are missing the data for any dates. So John's name shoul come for 3 Mar.

Is this possible..? I am using a calendar table for the below visual.

[ryan_mayu]

@BI_Analyticz 

your sample data is too simple. please try to add more data with different names and also provide the expected output.

[BI_Analyticz]

I have added more as requested. If you can see We have Yes, No as values in Submitted column. I have used Calendar table and successfully showing "No Data" too for the dates wherever the person is not having an entry as shown in 2nd screenshot. Now I want a slicer as shown in the 3rd screenshot.

[ryan_mayu · Accepted solution]

@BI_Analyticz 

here is a workaround for you.

1. use dax to create a new table

Table 2 = 
VAR a=DISTINCT('Table'[Name])
return CROSSJOIN(a,'Table (2)')

2. create a new column in the new table

Column = 
VAR _status=LOOKUPVALUE('Table'[Submitted],'Table'[Name],'Table 2'[Name],'Table'[Date],'Table 2'[Date])
return if(ISBLANK(_status),"nodata",_status)

please see the attachment below

[BI_Analyticz]

I have no words to praise you. This solution is really gonna help a lot like me. It worked like a magic. Real discovery... Thanks a lot.

[ryan_mayu]

you are welcome

[amitchandak]

@BI_Analyticz , You have create a measure like this

new measure =
var _max = max(Table[Staus])
if(isblank(_max), "No",_max)

Join it with an independent table having value Yes and no, and create a new measure that use this table with values yes and no

refer to the video of dynamic segmentation , how to approach this

https://youtu.be/CuczXPj0N-k

or

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[BI_Analyticz]

@amitchandak or let me put it another way. I want to filter out the dates for which there is no data for the names.

[BI_Analyticz]

I am not able to understand this technique since I am very new. Can you please attach a pbix so that I could figure it out.