Hi everyone!I've got some issues trying to represent some data in a line chart. I want to represent in a line chart, two lines, one describing the number of records/documents requested and another line with the number of records/documents resolved. Both lines will show the number of records (y axis) by months (x axis). The problem is that I'm provided with two columns: month of request and month of resolution. The months are specified as numbers, so January as number 1 and so on. I created another table relating each number to their corresponding month name, and created a relation between the month number and the number in the column "month of resolution". When I try to create the same relationship with the column "month of request", things don't work so well.So every row represents a record or document. And the values shown in the columns are the months. So for the first row of the image, the record was requested in January (1) and resolved in March (3). I'm asking for some guidance to solve this problem. How can I create the desired line chart with this data I own? What columns should I insert in Axis, Legend and Value? Thanks in advanced

I would reshape the table as follows(In PowerQuery):1. Split the table into two separate tables, say "RequestMonth" that holds the "Month of Request" column and "ResolutionMonth" which holds the "Month of resolution" column.2. Change the name of the "Month of..." column in both tables to "month_num"(For example).3. Add a "Type" column to each of these tables, with the value "Request" for all rows in the RequestMonth table and the value "Resolution" for all rows in the ResolutionMonth table.4. Combine both tables into a single one. You'll get a two-column table, one that holds month numbers and one that specify what that month number represents. Now, since you have a single "month_num" column, creating a relationship between it and the month names table will not cause ambiguity. I would then proceed to populate the field buckets of the Line Chart as follows:Axis: "Month Name".Legend: "Type".Values: "Count of month_num". BONUS STEPS:1. Add another column to the table(My personal preference would be in DAX) called "date" with the following formula-"date = IF(ISNUMBER([month_num]), DATE(1972, [month_num], 1), BLANK())".2. Since its value is a date, when you'd put it in the Axis field bucket, Power BI would allow you to select a sub-value of that date from the date hierarchy to be used instead of the value itself(By clicking the little triangle on the right side of the "date" field in the bucket). So if you choose "Month", it will show the give back the corresponding month names, in consideration with Power BI's localization settings, and better yet, it would absolve you from the need to create the "Month Names" table and defining the relationship with it. Note:I arbitrarily passed the value 1972 as the "year" parameter for the DATE() function since we only care for the months in the case you presented, so the year value has no meaning. ALTERNATIVE STEP:Instead of putting the "Type" field in the "Legend" field bucket and "count of month_num" in the the "Values" field bucket, you can add two DAX measures to the table:Records/Documents Requested = CALCULATE(COUNTROWS([month_num]), FILTER(tablename, [Type] = "Request"))Records/Documents Resolved = CALCULATE(COUNTROWS([month_num]), FILTER(tablename, [Type] = "Resolution"))And put them in the "Values" bucket, which will make the Line Chart's legend and Tooltips show the measure names to describe each line, instead of the values we put in the "Type" field. Final note:I assumed here you know how to reshape tables in PowerQuery the way I described above. Should you require further guidance performing those steps, feel free to ask.

Help representing data in line chart | Microsoft Fabric Community

Forum Discussion

Related content

line chart not representing zero values

Representing Multiple measures in sunburst chart

Representing overspends above 100% in a 100% stacked column chart

Vertical line to represent todays current date (dynamic) on a stacked bar chart

Stacked Bar Chart Timeline Gantt Chart