Forum Discussion

Asky82's avatar
Asky82
Frequent Visitor
4 years ago
Solved

Use single filter to affect multiple columns

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 datase...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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's avatar
    Asky82
    Frequent Visitor
    4 years ago

    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