Use single filter to affect multiple columns

[Asky82]

Hi,

I'm new to PowerBI and have been scouring the web trying to find an answer to my problem but to no avail!  

In summary I would like to use a single filter to affect 2 columns in my dataset.  I have a Fact table with a SeqFollowUpDateKey column joining to a date table and another column SeqClearingCutOffDateKey joining to another date table.  I want to use a single filter (we'll call it "Date Selection") the user can set, that searches all rows where the ClearingCutOffDate is After the Date Selection and the FollowUpDate is On Or Before the Date Selection.  To illustrate this I have hardcoded this into a page level filter and set both to 02/09/2022, please see below:

The idea is that the user can select any date and it will show the volume/value figures relevant to that date.

There is a lot more to the data model than this but I've simplified below to show the relationship to the relevant tables:

I know I could use 2 filters, 1 for ClearingCutOffDate and 1 for FollowUpDate but the user would always be setting the same value for both.  If anyone can recommend a solution where a single filter is used where I can then use that Date Selection to restrict these 2 dates as discussed that would be fantastic please!

Thanks

[Asky82]

I managed to get this to work, however only if I include both date columns in the visual.  Below is my code for the measure:

Fact row is visible = 
var slicerDate = value(format(SELECTEDVALUE('DateFilter'[Date]),"YYYYMMDD"))
return
IF ( SELECTEDVALUE('EDW FactDebtFollowUpContact'[SeqFollowUpDateKey]) <= slicerDate && SELECTEDVALUE('EDW FactDebtFollowUpContact'[SeqClearingCutOffDateKey]) > slicerDate, 1,0 )

You can see I've applied the logic directly to the keys in the fact table rather than the related dates in the Dim.  These keys are integers but stored in format yyyymmdd.  I created a test visual including both keys, a count and the Fact row is visible measure.  When I select from the DateSelector slicer it filters the results as expected:

But when I apply the same filter to my visualisation that does not include SeqFollowUpDateKey and SeqClearingCutOffDateKey, it does not work.  It's as if it is aggregating the data first to level of the visual and then applying the filter, but at this point the data is not at the correct grain to compare SeqFollowUpDateKey/SeqClearingCutOffDateKey to slicer date.  So instead of the desired count of 218,928 which I know to be correct, instead I get 1!  Please see below:

If I include SeqFollowUpDateKey and SeqClearingCutOffDateKey in this visual I get the correct result but obviously the visual looks terrible as it's at the incorrect level and performance is very poor.

Any suggestions on how to work round this would be much appreaciated please.  Thanks again!

[johnt75 · Accepted solution]

I think you need to modify the measures which you are putting onto the visuals to incorporate the filter. You can amend them to be along the lines of

Filtered Measure = 
var slicerDate = value(format(SELECTEDVALUE('DateFilter'[Date]),"YYYYMMDD"))
return
IF ( SELECTEDVALUE('EDW FactDebtFollowUpContact'[SeqFollowUpDateKey]) <= slicerDate && SELECTEDVALUE('EDW FactDebtFollowUpContact'[SeqClearingCutOffDateKey]) > slicerDate, 1,0 )
return CALCULATE(
original measure definition,
FILTER('EDW FactDebtFollowUpContact',
'EDW FactDebtFollowUpContact'[SeqFollowUpDateKey] <= slicerDate && 'EDW FactDebtFollowUpContact'[SeqClearingCutOffDateKey] > slicerDate
)

[Asky82]

brilliant!!  thanks so much.  I created a calculated measure per you advice and report now works a treat

FilteredVolumeOfAccounts = 
var slicerDate = value(format(SELECTEDVALUE('DateFilter'[Date]),"YYYYMMDD"))
return
//IF ( SELECTEDVALUE('EDW FactDebtFollowUpContact'[SeqFollowUpDateKey]) <= slicerDate && SELECTEDVALUE('EDW FactDebtFollowUpContact'[SeqClearingCutOffDateKey]) > slicerDate, 1,0 )
 CALCULATE( sum('EDW FactDebtFollowUpContact'[VolumeOfAccounts]),

FILTER('EDW FactDebtFollowUpContact',
'EDW FactDebtFollowUpContact'[SeqFollowUpDateKey] <= slicerDate && 'EDW FactDebtFollowUpContact'[SeqClearingCutOffDateKey] > slicerDate
))

[Asky82]

Thanks for the fast response!  I'll try this right now and let you know how I get on 

[johnt75]

You could create another date table not connected to anything, just for use in the slicer. The create a measure like

Table row is visible = 
var slicerDate = SELECTEDVALUE('Slicer Date'[Date])
return
IF ( SELECTEDVALUE( 'Table'[Clearing cutoff date]) > slicerDate && SELECTEDVALUE('Table'[Follow up date]) <= slicerDate, 1 )

then use that measure as a filter on your visuals to only show when the value is 1. You would need to add the measure to each visual separately, you can't use a measure as a page or report level filter.

[Asky82]

Ok so still having a few issues but must be nearly there!

Created a date table called DateFilter using the code below to list dates in last 2 years.

DateFilter = CALENDAR(FORMAT(UTCTODAY()-730, "General Date") , FORMAT(UTCTODAY(), "General Date") )

Then created a measure "Table row is visible" in the DateFilter using the code below:

Table row is visible = 
var slicerDate = SELECTEDVALUE('DateFilter'[Date])
return
IF ( SELECTEDVALUE('EDW DimDate - FollowUp'[FollowUpDate]) <= slicerDate && SELECTEDVALUE('EDW DimDate - Clearing Cut Off'[ClearingCutOffDate]) > slicerDate, 1 )

Then added filter on visual Table row is visible is 1, however this return no results.  Is there something obvious I have missed?

Thanks