Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

filter dates on multiple tables without relationship

Hello, 

 

I am having an issue with a model. I have five tables that need to be linked using an ID, which works fine. But in addition, I would like to have a general Date filter that filters all those five tables at once. 

 

Note that I can't create a second relationship using the Date columns, because I need my ID relationships to go both ways. The second relationship creates ambiguity. Here is the model: 

 

 

An advice I got was to filter dates in every measure I create using DAX. for example: 

 

Distinct.count =
var MinDate = min(DateTable[Date])
var MaxDate= max(DateTable[Date])
var val = values(AsRun[Date])

RETURN
CALCULATE(
DISTINCTCOUNT(AsRun[TV Link]);
Filter(val; AsRun[Date] >= MinDate);
FILTER(val;AsRun[Date] <= MaxDate))
 
The problem is that all pages still show all the dates (in tables, slicers,...). Only the final results of the calculations are filtered. 

 

Does anyone know a way to get around this problem? 

 

Thanks a lot,  and happy new year.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    According to my understanding, you want to use Date for Slicer to filter all other tables when there are relationships among the other tables but no between Date table and the others, right?

    Please try to use the following formula and then apply it(set as "is 1") to all visuals:

    Measure =
VAR _min =
    MIN ( 'Date'[Date] )
VAR _max =
    MAX ( 'Date'[Date] )
VAR _idTab1 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table1' ),
            MAX ( 'Table1'[Date] ) >= _min
                && MAX ( 'Table1'[Date] ) <= _max
        ),
        [ID]
    )
RETURN
    IF ( MAX ( 'Fact'[ID] ) IN _idTab1, 1, 0 )

     

    The final output is shown below:

    Here is the pbix file.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    According to my understanding, you want to use Date for Slicer to filter all other tables when there are relationships among the other tables but no between Date table and the others, right?

    Please try to use the following formula and then apply it(set as "is 1") to all visuals:

    Measure =
VAR _min =
    MIN ( 'Date'[Date] )
VAR _max =
    MAX ( 'Date'[Date] )
VAR _idTab1 =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table1' ),
            MAX ( 'Table1'[Date] ) >= _min
                && MAX ( 'Table1'[Date] ) <= _max
        ),
        [ID]
    )
RETURN
    IF ( MAX ( 'Fact'[ID] ) IN _idTab1, 1, 0 )

     

    The final output is shown below:

    Here is the pbix file.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • TBhelpneeded's avatar
      TBhelpneeded
      Regular Visitor
      2 years ago

      Hello!
      I just stumbled upon the same problem, but sadly cant open the pbix file you attached, I went through the process, used this code as per the last reply :

      0DateFilter =
      var MinDate = min('Calendar'[Date])
      var MaxDate= max('Calendar'[Date])

      RETURN
          IF ( OR(FIRSTDATE(WorkLog[Datecolumn])>MaxDate,LASTDATE(WorkLog[Datecolumn]) < MinDate),0,1),  measure shown no erro but i couldnt use it as a filter in my visuals since this  error popped up when used, any advice ?

      Thanks a lot !

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks  Anonymous, 

     

    I had to adapt your formula because I want the tables to be each filtered by date, independently of IDs. 

     

    So I used this formula as a filter in each visual and it does the job: 

     

    Filter Date =
    var MinDate = min(DateTable[Date])
    var MaxDate= max(DateTable[Date])
     
    RETURN
    IF(OR(FIRSTDATE('table1'[Date]) > MaxDate; LASTDATE('table1'[Date]) < MinDate);0;1)

     

    Thanks for your replies.

     

    Regards.

     

     

     

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Ideally, you should be able to join with date table, all these 5. Make some bi-directional joins as single directional and that will work.

     

    Or try measure like

     

    Distinct.count =
    var MinDate = minx(allselected(DateTable), DateTable[Date])
    var MaxDate= maxx(allselected(DateTable),DateTable[Date])
    var val = values(AsRun[Date])

    RETURN
    CALCULATE(
    DISTINCTCOUNT(AsRun[TV Link]);
    Filter(AsRun; AsRun[Date] >= MinDate && AsRun[Date] <= MaxDate))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

     

    Best Regards,
    Eyelyn Qin