Forum Discussion
PBI filter: Include Null/Blank with Date From
- 4 years ago
Hi jilanis
Are you going to add filters in a calculation or filter data displayed in a visual? If you want to add filters in a DAX expression, you can try below mode
Measure = CALCULATE ( [Expression], FILTER ( 'Table', 'Table'[Starting_Date] <= SELECTEDVALUE ( 'Field1'[Date-From] ) && ( 'Table'[Closure_Date] >= SELECTEDVALUE ( 'Field2'[Date-To] ) || ISBLANK ( 'Table'[Closure_Date] ) ) ) )If you want to filter data in a visual, you can create the following measure as a flag. Use this measure as a visual-level filter on the visual and set it to show items when value is 1.
flag = IF ( SELECTEDVALUE ( 'Table'[Starting_Date] ) <= SELECTEDVALUE ( 'Field1'[Date-From] ) && ( SELECTEDVALUE ( 'Table'[Closure_Date] ) >= SELECTEDVALUE ( 'Field2'[Date-To] ) || ISBLANK ( SELECTEDVALUE ( 'Table'[Closure_Date] ) ) ), 1, 0 )For above methods, your "Date-From" and "Date-To" should come from two independent date tables which don't have relationships with other tables.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi jilanis
Are you going to add filters in a calculation or filter data displayed in a visual? If you want to add filters in a DAX expression, you can try below mode
Measure =
CALCULATE (
[Expression],
FILTER (
'Table',
'Table'[Starting_Date] <= SELECTEDVALUE ( 'Field1'[Date-From] )
&& (
'Table'[Closure_Date] >= SELECTEDVALUE ( 'Field2'[Date-To] )
|| ISBLANK ( 'Table'[Closure_Date] )
)
)
)
If you want to filter data in a visual, you can create the following measure as a flag. Use this measure as a visual-level filter on the visual and set it to show items when value is 1.
flag =
IF (
SELECTEDVALUE ( 'Table'[Starting_Date] )
<= SELECTEDVALUE ( 'Field1'[Date-From] )
&& (
SELECTEDVALUE ( 'Table'[Closure_Date] ) >= SELECTEDVALUE ( 'Field2'[Date-To] )
|| ISBLANK ( SELECTEDVALUE ( 'Table'[Closure_Date] ) )
),
1,
0
)
For above methods, your "Date-From" and "Date-To" should come from two independent date tables which don't have relationships with other tables.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
- jilanis4 years agoRegular Visitor
Thank you. Much appreciated v-jingzhang.
I understand the concept of both solutions. We will give it a go. Thanks again.