Forum Discussion

trogne's avatar
trogne
Icon for Helper I rankHelper I
2 years ago
Solved

Two tables linked by date

I have 2 queries, each with a date column.

 

I want to link those date columns, so that I can use only one date slider for both queries.

 

I tried to create a relation with the 2 date columns (one-to-one), but it does not work. I don't see a new merge date field that I can use.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,trogne 

    Thanks for the quick reply.

     

    Yes, you are right, "time" is a calculation table.

     

    Secondly, regarding the issue that your calculation table cannot lock the relevant columns, you must have noticed from the pbix file we shared earlier that the calculation table is working fine in our file, so we speculate that this may be a version issue, so we recommend that you try to download the latest version.

     

    Here is the official download link for the latest version:

    Download Microsoft Power BI Desktop from Official Microsoft Download Center

     

    Secondly, you can also try the following two calculation tables in your version, I hope it will help you:

    time = 
VAR time1=SELECTCOLUMNS('Forge','Forge'[Date])
VAR time2=SELECTCOLUMNS('Local1234','Local1234'[Date])
RETURN UNION(time1,time2)
    time = 
VAR time1=SELECTCOLUMNS('Forge',"1",'Forge'[Date])
VAR time2=SELECTCOLUMNS('Local1234',"2",'Local1234'[Date])
RETURN UNION(time1,time2)


    You may need to note that if you re-calculate the following two columns, the names of the columns generated by it may change, and you will need to reconfigure the table relationships.

     

    Here are the relevant links:

    SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn

    UNION function (DAX) - DAX | Microsoft Learn

     

    If you have any new discoveries, please feel free to contact us.

     

    Best Regards,

    Leroy Lu

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

8 Replies

  • trogne's avatar
    trogne
    Icon for Helper I rankHelper I
    2 years ago

     

    Here I have 2 date sliders, but I would like to have only one general date slider :

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks for the reply fromfoodd,please allow me to provide another insight:

      HI,trogne 

      Regarding the issue you raised, my solution is as follows:

      1.First I have created the following table and the column names and data are the data you have given:

      2.Create calculated tables references:

       

       

      time = 
VAR time1=SELECTCOLUMNS('Forge',[Date])
VAR time2=SELECTCOLUMNS('Local1234',[Date])
RETURN UNION(time1,time2)

       

       

      3.Next create relationships between tables:

      4.Here's my final result, which I hope meets your requirements.

      Please find the attached pbix relevant to the case.

       

      Best Regards,

      Leroy Lu

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

       

       

       

      • trogne's avatar
        trogne
        Icon for Helper I rankHelper I
        2 years ago

        Thanks. 

         

        I understand that your first step, is just to have your data.

         

        So I start with "calculated tables references" :

         

        Is "time" a "Table" ?

         

        I'm trying to select the date columns :

         

         

         

         

        It does not recognize my columns.

         

         

         

         

         

         

         

         

         

         

         

         

    • cath1ynn's avatar
      cath1ynn
      Icon for Resolver II rankResolver II
      2 years ago

      There's 2 ways you can do it:

      1. Create a date dim table and link that to your 2 data tables using the Date field then using the Date field from the date dim table in your date slicer, OR

      2. Link your 2 data tables using their Date fields such that the Cross-filter direction is set to Both

      When you create a date slicer using the date field in one of your data table, it should filter both datasets

       

      Before changing date slicer:

      After changing date slicer: