Scatter plot doesn't match points rightly

Anonymous，

In this case, you would need to create the Month name column and Month no column in SQL data source.

Regards,
Lydia

I don't understand an element: the table with 2 columns, one for names and one for numbers of months, must be imported? Or I can build it with DAX formulas? I don't think I should use DAX because you said that in this way I would not be able to use it to order, but you said also to "create a new column", so what should I do?

When I select a chart with months on x-axis, the Sort-by-column option is not available.

Thanks

First thing you have to select the column name (right most side) not the visual and then go to sort option in modeling tab (check the image below).

(Column is selected - Month Name in the below example )

I will explain rest with example - let's say i have a date column

Date (dd-mm-yyyy)

01-01-2008

01-02-2008

01-03-2008

02-03-2008

now i create a new column month name and month no 1 and month no 2 with dax

Month Name = Left(Date.month,3)

Month no 1 = Date.month no

Month no 2 = case( Month Name, "Jan", 1, "Feb", 2, "Mar", 3)

I will get the below table

Date (dd-mm-yyyy)  | Month Name | Month No 1 | Month No 2 |

01-01-2008             |       Jan        |          1       |         1        |

01-02-2008             |       Feb        |          2       |         2        |

01-03-2008             |       Mar        |          3       |         3        |

02-03-2008             |       Mar        |          3       |         3        |

Now Lets try to sort Month Name by Month No 1 --- No issues

Now Lets try to sort Month Name by Month No 2 --- Issue

Why ?

Month No 2 is derived from Month Name (the column which we are trying to sort) where as Month No 1 is derived from Date column.

Follow these steps you will understand.

Thanks

Vinayak

Well kohlivinayak thank you very much, I read it and it works.

However, I was able to complete it only importing a database from Excel, while using Sql Server makes impossible one of the steps you explained, which is creating the Month Name column with DAX formula Month Name = Left(Date.month,3).

It's because I am not able to add ".month" after "Date", just like if Power BI does not recognize the date type. I change every possible datatype in Sql Server to my date column, but didn't find a way to use Month Name = Left(Date.month,3) with date not coming from Excel.

Thanks!

Anonymous, 

You can create these columns in Query Editor of Power BI Desktop. For more details, please review attached PBIX file.


Regards,
Lydia

Hi Anonymous

your attached file is full of error messages and can't help me. Then, I can't use Query Editor with data imported from SQL Server in Direct Query mode.

Nick

Anonymous

I downloaded you file and learned what you mean, but when I try to do it in my own file with my SQL Server database (not SSAS), the column is created but it shows "this step creates a query which is not supported in Direct Query mode".

Nick

Anonymous，

What is the data type do you set for your columns in SQL Server database?

Regards,
Lydia

Anonymous

The data type for the column with dates in SQL Server is datetime. If I use "Add new column" in Power Query as you did in your file, using Text.Start(...), it returns the message I wrote in my previous message.