Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago

Multiple tables with multiple columns of dates, blank data using USERELATIONSHIP

Good day everyone,

I'm creating a performance report of the company's salespeople, and the source of the data is in the sales table and the tables of our CRM.

I have a calendar table in the report, which has the following relationships:

Calendar[Date] - Sales[Date] (Active)
Calendar[Date] - All Deals[Date Created] (Active)
Calendar[Date] - All Deals[Closing Date] (Inactive)

From the 'All Deals' table I'm interested, for the moment, in counting the number of deals created and the number of deals won. And my measurements are as follows:

Number of Deals Created =
CALCULATE(
COUNT('All Business'[Record ID]),
ALLSELECTED(Calendar[Date]),
USERELATIONSHIP('All Business'[Date Created],Calendar[Date]),
FILTER('All Business', 'All Business'[Date Created] <> BLANK())
)

Number of Deals Won =
CALCULATE(
COUNT('All Business'[Record ID]),
USERELATIONSHIP('All Business'[Closing Date],Calendar[Date]),
FILTER('All Business', 'All Business'[Business Stage] = "Closed Won")
)
These measures work when there is no date filter in the report.

But, as soon as I place a date filter brought in from the calendar table, it sends me blank results. (If I have data in this date range)

Does anyone have any ideas on how I can fix it? I'm interested in the filter being direct from the calendar table, because on a single page I'd like to have the sales and CRM metrics. It should be noted that this filter does work with the measurements in the sales table.



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Epinedo ,

    It sounds like because the date column in the table with the relationship you are using as a slicer is causing your visual objects and MEASURES to be affected by multiple filters. In general, I would recommend that you add an additional date table that has no relationship to any of your current tables and then use that in DAX:

    'Calendar'[Date] = SELECTEDVALUE('NewTable'[Date])

    That's just one example I'll give you. The exact solution will have to be changed to suit your situation. Could you please provide a screenshot of the sample data and the relationship between the tables, it would be helpful!

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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      2 years ago

      Hi Dino,

      Thank you for your reply, but I think this didn't help fix my problem. Here I share with you a screenshot of what the relationships between my tables look like, I point in yellow to the date columns that I am relating.