Forum Discussion
Line and stacked column chart - line not working with data
The reason you get a flat line is that Power bi has no idea what to do with your line and it just makes it equal to everything (so if that value = 15, you'd see 15 for every role/date).
I'm thinking we can solve your issue by adjusting your tables and creating some relationships! We need some dimension tables. If you're unfamiliar, they're just tables that store attributes that define our big set of data.
Starting with the data you posted here we can make a role dimension table:- Right click on your resources_needed table and duplicate it.
- Rename 'Role_needed' to 'Role_available'
- Select Append Queries and append the resources avaible table
- Since the column names match, all of your roles will now be in one column.
- Select that column > right click the column header > remove other columns
- Right click again, and remove duplicates.
- Rename your column to Role and your table to something like Role_dimension
Then to create a date dimension table... (I like using this script since it gives you a number of useful columns https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query)
- New Source > Blank Query > Open advanced editor and copy/paste the script >rename to date or whatever floats your boat.
- Change the ToYear (near the top) to a date in the future
You should have 4 tables! Close & Apply to exit power query.
For relationships:
- Open the Model view
- Drag and drop from your dimension tables into your "Fact" tables (where your actual data is located)
- For role, you only have one so relate it to each of the roles in each table
- Do the same for date, just use the 'Date' at the top of the table
- Model should look something like this
-
- Use your dimension tables in your visuals
Let me know how it goes!
Yeah these are two different tables. There is no relation set between them as I didn't know what kind of relation would apply (no unique values are available in either table). I wanted to sort of indicate that both role and date were the same kind of variable but didn't know how to and whether that was necessary.
The data comes from the two different tables, reaources_needed and resources_available.
Im using this variables from the first table:
Hours_available (uren): y line
These from the second table:
hours_needed (uren): as column value
Role_needed: legend
Date_resource: x axis
Resident RockstarThe reason you get a flat line is that Power bi has no idea what to do with your line and it just makes it equal to everything (so if that value = 15, you'd see 15 for every role/date).
I'm thinking we can solve your issue by adjusting your tables and creating some relationships! We need some dimension tables. If you're unfamiliar, they're just tables that store attributes that define our big set of data.
Starting with the data you posted here we can make a role dimension table:
- Right click on your resources_needed table and duplicate it.
- Rename 'Role_needed' to 'Role_available'
- Select Append Queries and append the resources avaible table
- Since the column names match, all of your roles will now be in one column.
- Select that column > right click the column header > remove other columns
- Right click again, and remove duplicates.
- Rename your column to Role and your table to something like Role_dimension
Then to create a date dimension table... (I like using this script since it gives you a number of useful columns https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query)
- New Source > Blank Query > Open advanced editor and copy/paste the script >rename to date or whatever floats your boat.
- Change the ToYear (near the top) to a date in the future
You should have 4 tables! Close & Apply to exit power query.
For relationships:
- Open the Model view
- Drag and drop from your dimension tables into your "Fact" tables (where your actual data is located)
- For role, you only have one so relate it to each of the roles in each table
- Do the same for date, just use the 'Date' at the top of the table
- Model should look something like this
-
- Use your dimension tables in your visuals
Let me know how it goes!
Crazy stuff, thanks for your thorough reply, it really helped me get a better grasp of the workings of power bi and the relational model. Thanks a lot, much appreciated!