Forum Discussion

devesqdeves's avatar
devesqdeves
Helper II
3 years ago
Solved

Filtering by Week but to show NULL values

Hello!

I am trying to filter a visual by Week but to get the values that have nulls in EndDate.

The 2 tables here presented have a 1-Many relationship, between EndDate(ConsultantStateTable ) and Date (Date).

I have tried to use StartDate to connect to the DateTable but it removes even more values.

Tables:

ConsultantStateTable - Has the ConsultantName,StartDate, EndDate,ConsultantState

Date - Has all the usual camps ( Date,WeekOfYear etc)

 

Calculated Column :

 

StateCountColumn =
CALCULATE(DISTINCTCOUNT('DW FCTConsultantState'[CandidateId]),
 ('DW FCTConsultantState'[EndDate] > min('Date'[Date]) || 'DW FCTConsultantState'[EndDate]= BLANK())
 && 'DW FCTConsultantState'[StartDate]<max('Date'[Date])))
 
Filters :
Week Of Year Filter
Year
 
Visuals:
A matrix with Consultant Name, Startdate, EndDate  ConsultantState and StateCountColumn

 

The problem is:

 

I select the Week 7 for example, the measure should show in visual all the consultants that have the values with

(EndDate > first day of the week  7 OR EndDate = BLANK() ) and Startdate < the final of that week 7) , WITHOUT FILTERING THE NULL VALUES. This is the most important part. But the week is filtering all the NULL values with EndDate = blank(), and i need to see them in visual. I know that this is a normal behaviour, but i cannot find a way to go around that, could someone please help?

 

Thanks for your time!


 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi devesqdeves 

     

    Since you need to compare StartDate and EndDate with the calendar dates at the same time, you need to break the relationship between ConsultantStateTable and Date table. With the current relationship between EndDate(ConsultantStateTable ) and Date (Date), NULL EndDate value cannot match any date in Date table, so it is filtered out when you select a week. You have to remove the relationship and keep two tables disconnected. Then the measure should work. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

2 Replies