Forum Discussion
Scatter plot doesn't match points rightly
Anonymous，
Do you connect to SSAS database or SQL Server database in Power BI Desktop? If you connect to SQL Server using DirectQuery, you are able to use Query Editor in Power BI Desktop.
Also please open updated PBIX file.
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
Anonymous，
In this case, you would need to create the Month name column and Month no column in SQL data source.
Regards,
LydiaWell 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,
LydiaHi 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，
Do you connect to SSAS database or SQL Server database in Power BI Desktop? If you connect to SQL Server using DirectQuery, you are able to use Query Editor in Power BI Desktop.
Also please open updated PBIX file.
Regards,
LydiaAnonymous
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,
LydiaAnonymous
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.