Relationships and date tables

[Anonymous]

Hi all,

I am trying to create a funnel with measures based on data from two different tables. Each table has a separate "date" field they use for a true repsentation of the # of contacts/leads/opportunies created during a specificied time with specific criteria. Some contacts are only found in each of the tables, so I do not want to filter from one table to the other as it doesn't show a true representation of the total number. I want to show the below view that has only 1 date slicer to filter by year, but won't filter from one table to the next. Below is an example (the first two scorecards are measures from one table (dv_hub _contacts) and the remaining are from (dv_lead_to_opty):

In order to use just one date, I created a date table and connected each date field as the active relationship (see below):

However, when I added a slicer with the date field from the date table, whenever I filter by year I get a 0 value for all my measures. Why is this happening and is there another way to accomplish this? Thank you!

[Anonymous]

Hi @Anonymous 

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

[moncx]

Hey,

check the fields that use use to connect date table with other tables, because if you get 0 value it probably does not connect correctly. There might be several options depending on your data, but you can start foolproofing by

- creating seperate table and putting date from date table and some field from other two tables (dv_lead_to_opty and dv_hub_contacts) - then you will see if the tables connect correctly (you should see values from both tables - date from date and corresponding name/country/etc. that you chose from other table) if you don't see anything chech your date collumns because something is wrong with it.

-If you see values then something wrong with measures, maybe you use date filters that do not work with date table or something similar depending on the data. Add measure to the additional foolproofing table and see if the problem is with all the dates or only with some of them.

Go step by step with all your data and you will find the problem somewhere.