Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Common slicer for multiple date table

Hi All ,  

 

May I get your help to create a common FY quarter filter  on 3 date tables . 

- Each date table is link to material target finish date in each table 

- Every  material tables has different target finish date and I created date table to link the target finish date column. 

 

What have I tried 

  1.  Used single date table to filter  accross 3 tables ; only 1 out of 3 relationhip is active , the other 2 are inactive. -> Get wrong material count 
  2.  Created individual date table which link to target finish date and also links to another Date table as filter ; Failed due to only 1 active relationship as screenshot below. 

 

My file is here : 

Sample pbi file  

 

Any help is much appreciated ,  Thanks in advance !! 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , This will not work like this.

    You need to create an independent date table and use values from that in measures.

     

    example

     

    Measure =
    VAR _max = MAXX(allselected('Date1'),'Date1' [Date])
    var _min = MinX(allselected('Date1'),'Date1' [Date])
    return
    calculate(sum(Table[value]), filter(('Date') , 'Date1' [Date] >= _min && 'Date1' [Date] <=_max ))

     

     

    Also, refer

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

  • Calvin69's avatar
    Calvin69
    Icon for Helper III rankHelper III
    5 years ago

    Hi Anonymous ,

    Change the relationship types from "Many to Many" to "Many to Single" \ direction of the arrow should be Date_Filter to the other tables.

    If you do the above, it should allow you to activate all three relationships at the same time budd.

     

     

    Hope this helps

    H

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Calvin69 , 

      Thanks and appreciate your  reply. 

      I tried ur method  which is changed to  Many-to-One relationship, it became active but in wrong filter direction and i couldnt change it. 

       

       

       

       

      • Calvin69's avatar
        Calvin69
        Icon for Helper III rankHelper III
        5 years ago

        Hi Anonymous , 

        Try what amitchandak  suggested below and if it does not work, share with me a copy of that report file to have another look at it.

        H