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 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.
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
8 Replies
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
or
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualizationamitchandak or let me put it another way. I want to filter out the dates for which there is no data for the names.
Anonymous
your sample data is too simple. please try to add more data with different names and also provide the expected output.
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
