Forum Discussion
Use single filter to affect multiple columns
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 )
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!
Super UserI 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
)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 ))