How to create a line chart / scatter chart with text labels in Y-axis and date hierarchy in X-Axis

[Anonymous]

Hi, 

I'm trying to do something like this in Power BI :

I want to have a graph with categorie labels in the Y-axis and a Hierarchy date in the X-Axis (Year, Semester , month) like displayed in the picture below.

My value are 2 dates for each category : a "date of making" and a "date done" that I want to display on the chart depending on the categorie and on the date.

My data are two Excel files, the first one contains the columns : Categorie_label, Date making, Date Done and the second one contains the data values (Date, year, semester, month)

I've triyed to use a line chart and a scatter chart but they don't accept no numeric values for the Y-Axis. I've searched topics on internet but didn't find an answer. Here another topics talking about something similar  : https://community.powerbi.com/t5/Desktop/HowTo-Create-line-graph-with-text-labels-and-dates/m-p/2896...

And here's even a proposition to create this feature : https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/32027089-create-line-graph-with-t...

I would like to know if there is any visualization in Power BI that allow us to do that or any tips to do it with the actual visualizations.

Thank you ! 

[v-lid-msft]

Hi @Anonymous ,

We can use line chart as a workaround if the catagories is format as "Constant String" + Number such as the example you have shared:

First of all, we use Power Query Editor to transform the table to another format (please make sure you have backup of your origin query)

1. group by the category

2. add a custom column use following formula

Table.UnpivotOtherColumns(
    Table.RemoveColumns([Date],{"Categorie_label"}),
    {}, 
    "Status", "Date"
)

3. expand the new column

all the queries:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZW0lEyMjAw0DfUN0QwTZVidYCScBEjJEkQgkgbQcQM9Y1h0ob65iC9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Categorie_label = _t, #"Date making" = _t, #"Date Done" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Categorie_label", type text}, {"Date making", type date}, {"Date Done", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Categorie_label"}, {{"Date", each _, type table [Categorie_label=text, Date making=date, Date Done=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.UnpivotOtherColumns(
    Table.RemoveColumns([Date],{"Categorie_label"}),
    {}, 
    "Status", "Date"
)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Status", "Date"}, {"Custom.Status", "Custom.Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date"})
in
    #"Removed Columns"

Then we can create a measure to calculate the category

Category = VALUE(SUBSTITUTE(SELECTEDVALUE('Table2'[Categorie_label]),"C",BLANK()))

Set the format as "C" + number in Model View for this measure

Then we can use it in the line chart:

Best regards,

[Anonymous]

Hi,

Thank you for your answer I really appreciate the effort done.

However the categories label were just exemples, in my real data I don't have C1, C2 and C3 but I have real values (text label) from the database.

[v-lid-msft]

Hi @Anonymous ,

Sorry for that we did not have better solution, but if your categories is constant and only have a few , we can create multi measures with custom format to meet your requirement.

Best regards,

[Anonymous]

Thank you. Our categories are not constant and differ from each other and also they are not few.

However thank you so much !

[v-lid-msft]

Hi @Anonymous ,

How about using matrix visual with the x-axis above?

If there is not what you want, you could create your own custom visual if you are an experienced web developer.
https://powerbi.microsoft.com/en-us/documentation/powerbi-custom-visuals/

Best regards,

[Anonymous]

Hello,

Thank you for your answer it's really good but our cutomers want a very specific visualization with hierarchy for the date and some other things.  I don't think that a matrix will be helpful. Maybe we'll think about making our own Visualization. 

Thank you so much for your answers !