creating a data trend from two tables

[merinadica]

I have been trying to create a trend visual from two tables and I dont find the best way to do it.

the table captures the status of a job request each week which is appended.

In another excel sheet I have the weeks date

I would like to create a graph that shows the trends over time. Something like the below picture:

[MFelix · Accepted solution]

Hi @merinadica,

You should unpivot w1, w2, w3 from your table 1 then remove the w and make a relationship between both tables then add the values to a line chart.

See attach PBIX file with data and example.

Regards,

MFelix

[merinadica]

Hi @MFelix

Thank you for your response. it worked just fine. I have a follow up question. I was successfully able to unpivot in the query editor and reproduce the same graph for my example. However, in my original problem, the data is creating by post processing the data from another sources.

I use various DAX functions to create the columns in the modeling section of power BI desktop. Is there a way to use DAX function to unpivot the columns dynamically? Every week the number of the columns will be added. In the example the data were shown for only 3 weeks.

[MFelix]

Hi @merinadica,

You cannot unpivot columns with DAX.

Is your columns of the weeks variable, or are you adding information a week at a time?

Regards,

MFelix