Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    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

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
5 years ago

Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      5 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.