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 th...
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.

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 !