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

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

Anonymous , 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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