CODEWITHSUNDEEP
datechartspowerbirelationship
Burner
Burner

Reputation: 57

Using 3 date fields over 2 tables in Power BI

I am new to Power BI and need to display a visual showing data requests, deadlines and returns. I have the idea of a line and clustered bar chart - the line to show the amount of notices given to a business from the date a data notice is given to the deadline date. The bars can then show the amount of data received.

I have two table in Power BI. Notices and Historical Data.
Notices contains the fields - Business Name, Amount of Notices, Request Date and Deadline.
Historical Data contains the fields - Business Name, Amount Returned and Return Date.

I have the tables in a relationship using Business Name in a many-to-many join. I have tried several attempts to create charts however struggling with the fact there are 3 date fields. I cannot get them to show in one chart together. I attempted to create a table with the appended date fields from each table and then create relationships between the new date table and the original tables however this cannot be done with the original tables having an active relationship as well. The visual also needs to be able to filter using slicers etc so all relationships need to be active.

Any help would be greatly appreciated. Even another visual if recommended as I am new to Power BI. It's worth noting I would like to create a Gantt chart but do not have the add-on.
Thanks.

Additional info:
Tables are below. The business name can appear many times in each table as the requests are annually and they will have several data return records for each notice request.

Historical Data
enter image description here

Noticesenter image description here

Upvotes: 0

Views: 840

Answers (1)

Jon
Jon

Reputation: 4967

From your question I think the issue you have is that with the calendar table you can only have one active relationship on a table, so for example you should have something like this. (I have also added a Business Name table to remove cross filtering between the to tables).

I've used this example data:

enter image description here

And offset the deadline by 6 days

enter image description here

So by using the calendar table you can map to date the values for the 'Amount Returned' via the Return Data & 'Amount of Notices' by the Deadline date, but can't see values for the 'Request Date', and doesn't show them correctly.

enter image description here

So if 16th Jan is selected you'll get:

enter image description here

When it should just be a value of 15 for 'Total by Deadline'

What you have to do is create two measures one that uses the active and one that will use the inactive relationship via USERELATIONSHIP So for example:

    Total by Deadline = SUM(Notices[Amount of Notices])
    Total by Request = CALCULATE(SUM(Notices[Amount of Notices])
                      , USERELATIONSHIP('Calendar'[Date], Notices[Request Date]))

The USERELATIONSHIP forces the measure to use the inactive relationships, and should allow you to filter via selected dates correctly.

enter image description here

enter image description here

Upvotes: 1

Related Questions