How to create a Line Chart based on 3 tables while maintaining a hierarchical relationship.

[Syndicate_Admin]

Hello!

I have been trying tirelessly to solve this without success. Let's say I have 3 tables. One for cities, one for counties and the last for states. For each table, these are the columns: [Company] [Location] [Date] [Internet Speed].

ATTENTION!: The value of the Internet speed of a County or State is not equal to the average of the Internet speeds of the cities that belong to that state or county (this is because it is not just a simple average, but a weighted average with values that I do not have).

Of course, there is a hierarchical relationship table:

MY GOAL is to create an Internet Speed Lines vs Date Chart that can show 2 or more cities on the same OR 2 or more counties chart on that same OR 2 or more states chart on the same graph.

• What did I already try?

I tried combining the 3 tables and adding a column called Location Level (city, county or state) and also tried to leave the 3 tables separate, but creating a selector where you can choose if you want to see multiple cities, multiple counties or multiple states in that same exact graph. However, I've had trouble getting the dashboard end user to use only that selector to accomplish what I want because I manually changed the legend and date field of the line chart so that I could display all the options.

Would you be so kind as to help me or give me a hint? I asked chat gpt, bard and bing, but their answers were bad! I tried to create DAX measures as a basic switch that, depending on the value selected in a Location Level selector, would return different columns (city table location, state table location, or county table location) so I could place that in the legend field of the line chart display, but it doesn't seem to work (I tried using VALUES, DISTINCT, SUMMARIZECOLUMNS, but nothing works).

Thank you!

Ignacio Kairuz

[v-zhangti]

Hi, @Syndicate_Admin 

Based on the data you provided, what kind of output do you expect?

Best Regards,

Community Support Team _Charlotte

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