Legend Issues when filtering using Start and End Dates

[Ryan_K]

Hello,

I am new to using Power Bi, and I am struggling to understand an issue I am facing.

I have 3 tables in my model:

Events Table, which contains an Event Name, the specific date of the event, and the range of dates where I would like to present data for when that Event is selected, defined by Start and End Dates.

Date Table, which is a list of dates of the first of every month

Financial Data Table, which contains a list of companies, defined by "Excel ID" and certain financial data at specific dates.

I have created a Measure called Event Term that acts on the Date Table in order to filter based on the Start and End Dates in the Event Table. The script for this Measure is:

Event Term = 
VAR range_dates = MAX('Date Table'[Date])
RETURN
    TOCSV(
        FILTER(
            VALUES('Event Table'),
            'Event Table'[Start Date] <= range_dates && range_dates <= 'Event Table'[End Date]
        ),
        ,
        " - ",
        FALSE()
    )

This script allows me to create a Table Visual that appropriately shows a list of Events, the Date of the Event, the Date of the Financial Data, and the Value of the Financial Data.

However, when I try to visualize this data using a line chart, specifically when I add Event to the Legend, I receive an error message. Below is the line chart prior to adding Event to the Legend, and after adding Event to the Legend.

 

It suggests I need to edit the relationships in my Data Model, but I feel I've tried everything. Is there anything I can do to get this visual to work? Below is a screen shot of my Data Model.

 

 

[lbendlin]

Ask yourself:  Can I get there from here?  for all the fields in your visual. The arrows will tell you if you can or not.  For example you cannot  (and should not) control the Financial Data table from the Events table. The arrows are pointing in the "wrong" direction.  You can control both fact tables from the calendar table.  For some reason you decided to also control the calendar from the Financial data, which means you can control the Events from the Financial Data.  This may not be what you want though.

If you want to use data from disconnected tables you can use measures, and/or TREATAS for filter transfer.

[Ryan_K]

Thank you, this is very helpful for understanding the relationships I am making between the tables. I'm still not sure on how to solve for what I am trying to produce though. For the line graph, I am trying to get it to show two lines (one for each event) rather than just the sum of the two events. The events have overlapping date ranges, which I think is the issue at heart. It's also a bit confusing because I can show this in a table visual, but not in a line chart. Appreciate your help.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...