Filtering by Week but to show NULL values

[devesqdeves]

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!

[v-jingzhang · Accepted solution]

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.

[lbendlin]

To report on things that are not there you need to use disconnected tables (with your week numbers) and crossjoins or measures.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523