filter dates on multiple tables without relationship

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous · Accepted solution]

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]

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 !

[amitchandak]

@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))