Forum Discussion
How to filter data for no date rows
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...
Anonymous
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
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
Super User
Super UserAnonymous
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
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