Need help to build a report

[danielsouza]

I'm trying to build a graphic report, line ou bars, but it is not working:

I need something like this:

[shebr · Accepted solution]

Hi @danielsouza

You need to un-pivot your columns and link them to a date table.

1. First see this video and create a date table. https://youtu.be/vJ7xNUK8sQE

2. Next go to your query editor for your query, highlight the first column [Description] by clicking on the header, right click the header and click 'Unpivot Other Columns'. This will put your data in a better format. You then need to perhaps add a year and day to your Month and create a 'Date'.

3. Link the date to the date table in your relationships

4. You should then be able to create the graph as you showed.

Let me know how you get on.

Thanks

shebr

[danextian]

Hi @danielsouza,

I saw this coming when I saw the screenshot of your model in your other post. You data is in a columnar model so it has to be transformed into a tabular one. And yes, as @shebr mentioned, you need to unpivot all other columns in your data except  excep for descricao. If you have a total column, you might want to remove that first before unpivoting.

If you can post a dummy data with the same structure as in your screenshot, I can get you started with the query.

[danielsouza]

We are doing great so far.

Do you have any idea how to transform the column data, numbers to months?

Like 1=january, 2 = february...

[danextian · Accepted solution]

In DAX, you can do this:

Month Name =
FORMAT ( DATE ( 2017, 'Table'[Month Number], 1 ), "mmmm" )

You can replace 2017 with any year you want. The goal here to is to create a valid date and extract the month name from that date.

By the way, what are you going to do with 13?

[danielsouza]

13 was a totalized row, I already clean this up.

Is this a measure or calculated column? Is not accepting the parameters, something wrong?

[danextian]

Based on the error, you have entered it as a measure. Should be a calculated column.

[danielsouza]

Yeah, it must be a calculated column.

Succeded!

Many thanks man!

[shebr · Accepted solution]

Hi @danielsouza

You need to un-pivot your columns and link them to a date table.

1. First see this video and create a date table. https://youtu.be/vJ7xNUK8sQE

2. Next go to your query editor for your query, highlight the first column [Description] by clicking on the header, right click the header and click 'Unpivot Other Columns'. This will put your data in a better format. You then need to perhaps add a year and day to your Month and create a 'Date'.

3. Link the date to the date table in your relationships

4. You should then be able to create the graph as you showed.

Let me know how you get on.

Thanks

shebr