0

Exclude a specific relationship from a Discovery

Hi everyone,

I'm working with a data model that includes multiple relationships between fact tables and a date table. Currently, there's an existing relationship between a certain table and another dimension, but in one specific Discovery, I want to disable that relationship so a different one (specifically with the date table) can take effect instead.

Is there a way to make a certain relationship not apply or be ignored in a specific Discovery, without changing the overall data model?

Any suggestions or best practices would be much appreciated!

Thanks in advance,
Itay

5 replies

null
    • Customer Solutions Architect
    • Moshe_Yossef
    • 3 days ago
    • Reported - view

    Hi

    Can you share a screenshot of the model and mark the relationship you don't want to apply?

    Also I don't understand how come this relationship overrides the date dimension. Can you elaborate on that as well?

    • itay_karkason
    • 3 days ago
    • Reported - view

    Hi ,

    here’s the situation:

    • tblDateRange Table:
      I created this table (it’s not part of the original data) as a list of dates, and it works with the other tables. It contains a column called Date.

    • tblInvoice Table:
      This table includes a column called InvoiceDate, which is related to the Date column in tblDateRange.

    • tblProjects Table:
      This table also has a Date column, which is related to the Date column in tblDateRange.

    I have a report (Discovery) that displays invoices for each project by month. However, I need the controlling date column to be InvoiceDate rather than the Date from the tblProjects table. Due to how the Discovery is built and the relationships in the model, tblDateRange is currently showing the date from tblProjects. In this specific Discovery, I need the InvoiceDate to be represented in the Date column of tblDateRange.

    This distinction is very important because I have another Discovery where the date should be based on the InvoiceDate column.

    I am attaching screenshots of the relationships in the model for further clarity.

    o3-mini-high

     

     

      • Customer Solutions Architect
      • Moshe_Yossef
      • 2 days ago
      • Reported - view

       

      Ok, this looks like what you have is invoices that are joined with the project through a project I'd or something like that. Projects have dates, and invoices have dates, but they are not the same date, also there are multiple invoices per projects.

      Did I get this right?

      Consequently, when you filter the dates, pyramid uses the dates to filter the projects, and the projects to filter the projects.

      I presume you are showing measures from both invoices and projects table, is that correct?

    • itay_karkason
    • yesterday
    • Reported - view

    Hi, Projects have dates and invoices have dates.
    There is also a DateRange table that is connected to the other tables. Because of the relationship setup (as shown in the image I’ve attached), I’m running into an issue.

    Currently, I’m working on two discoveries:

    1. The number of invoices by invoice date

    2. The number of invoices by project date

    I want to stick as closely as possible to the DateRange date, since that’s the main slicer for my project. However, due to the relationship setup, I can’t — and in the report slide showing the number of invoices by project date, I have to use the project table’s date slicer instead. But then, I need it to work in parallel with the DateRange slicer, so I can use that one as a background filter.

    What should I do?

      • Customer Solutions Architect
      • Moshe_Yossef
      • 15 hrs ago
      • Reported - view

       

      Sorry still trying to understand.

      The relationship between project and invoices is one to many.

      Project have dates and invoices have dates.

      you want to see invoices by invoice date and also invoices by project date?

      Here is what you can do:

      Duplicate the date range table (you can do it in the data model, no need to load it twice).

      call one date range table "project dates", and the second one "invoice dates".

      set the relationship between the invoice and the projects to bidirectional.

      now you can use both dates.

      However: do notice that bidirectional relationships are risky - if you show a measure from the projects table together with a measure from the Invoices table you are likely to get a cartesian product.

      another alternative would be to create a separate direct query model on the same data base with different relationships.

      let me know if it helps.

Login to reply

Content aside

  • 15 hrs agoLast active
  • 5Replies
  • 19Views
  • 2 Following