Forum Discussion

bcng's avatar
bcng
Frequent Visitor
7 years ago
Solved

Using selected date in slicer to filter dates in another table

I have two separate tables: 1. "From" date 2. "To" date   Both tables have identical list of dates but I would like to use both as them as separate slicers.   For Example, I have dates from 1/1...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

So, typically the way you do that is to disconnect your slicers from everything using Edit Interactions. Then you create a measure that grabs the MAX or SELECTEDVALUE of each slicer and performs a calculation which returns a value if it is within that range or BLANK() or 0 otherwise and that essentially becomes your filter.

 

If that doesn't help, need more information to be more specific. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

bcng's avatar
bcng
Frequent Visitor
7 years ago

Hi Greg,

 

I tried using this measure:

Measure = IF('To Date'[Current Historical]>=VALUES('From Date'[Current Historical]),'To Date'[Current Historical],BLANK())
 
but I am getting this error:
A single value for column 'Current Historical' in table 'To Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 
Can't seem to figure out how to work around this error.
 
I have already created a relationship between the two columns
 
 
  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi bcng,

    Based on my test, you could refer to below steps:

    Create a measure:

    Measure = IF(CALCULATE(MAX('To'[Date]))>=SELECTEDVALUE('From'[Date]),1,0)

    Set the visual slicer:

    Result:

    But the measure could not be applied it in the slicer, I suggest you use the measure in visual.

     

    Regards,

    Daniel He