Scatter plot doesn't match points rightly

[Anonymous]

Hi,

my issue regards scatter plots. I have a scatter plot with months on x-axis and some integer numbers on y-axis. If I filter the data so that some month has no number (it should be 0, but actually there's no data), the scatter plot doesn't show the 0-mark but goes straight from the former month to the latter.

For example, if I have the following data visualized in a scatter plot:

Jan 5

Feb 3

Mar 1

Apr 1

May 1

Jun 4

Jul 5

Aug 8

Sep 2

Oct 1

Nov 1

Dec 2

And then I use a filter so that I get no data for March and April, the plot goes from 3 for February to 1 for May without showing 0 for March and April. 

It's senseless and I should fix it.

Please help me however you are able to.

Thank you very much

[kohlivinayak]

Hi @Anonymous

Whatever calculations you are doing do it in a measure and add a 0 in the last eg

measure = sum(column_name) + 0

it will sove the problem.

Moreover I will personaly prefer a line chart with stroke width as zero and enable show markup to plot the points. With this i will be able to show names of the month

Thanks

Vinayak

[Anonymous]

Hi @kohlivinayak and thank you for answering.

Unfortunately, it doesn't work. I created a measure adding 0 in the end but if I filter results the line still does not pass from 0 when it should.

Moreover I didn't understand what you mean with last sentence, starting with "moreover..."

Thanks

Nick

[kohlivinayak]

Hi @Anonymous

I guess you are not using any relation and is taking date from the same table.

I have created a powerbi file, in the table on right bottom i have created the same thing twice onw with date dimension and one without date dimension. Depending on your requirement use the case which suits you.

https://drive.google.com/open?id=1SZI6NaUmsjWVQKyYMztPYUliCaqbsDr3

I hope it helps.

Thanks

Vinayak

[Anonymous]

Stupid me, sorry @kohlivinayak, I talked until now about scatter plot while I am working with line chart...

I think what differs my report from yours is the data to use for values for line chart: I am working with a DirectQuery from SQL Server database and I am using "Count of column_name" as value. Then I work with filters, which make months with no data disappear, while your month names are fixed, stable. I think working with filters bothers me, I'd need that when you filter data you don't lose data with no values, because no values should mean 0.

[kohlivinayak]

No Issues case in scatter and line chart don't vary much.

So why mine is stable i have created a new table having the values of month. Created a relation with actual data table, and will create filter from this table

Table 1 ---> Actual Data

Table 2 (need to be created) ---> Only Month Name/ Month Year Name eg [1,2,3,....,11,12]

Create a relation between these two ( single side filter from table 2 to table 1 )

Create the filter on Table 1 - so that when you filter the table, table 2 values will not be affected, hence measure will be calculated for all the values.

If your filter filters out the values in table 2 you will not see those months hence no zeros.

Thanks

Vinayak

[Anonymous]

Well @kohlivinayak, I think the issue now comes to my database, because when working with DirectQuery from Sql Server I can't create new tables, only measures and columns. Don't you now any way to solve it without creating a new table?

[kohlivinayak]

Well i am not able to think of any solution with creating a new table as of now, but just a thought, you can create your new table with sql query in direct connection instead of in powerbi.

[Anonymous]

@kohlivinayak well yes I think there's no way better than this unfortunately.

Thank you very much for your great hints.

Let me ask you some more. In the example you sent me there were charts ordered by month names. I opened another post about it some time ago, because I didn't find a way to do that, only converting names to numbers was good, but that's not great. I noticed you built a new table in Power BI with dates and month number, getting dates from DAX Calendar function. Do you think this is way you get months names ordered chronologically or is there something else?

Thanks

[kohlivinayak]

Well we have an option of sort by column in modeling tab.

What we do is create a new column and assign values in which we want to sort them.

So now we will have a table like this

Month    |    Month No

Jan        |         1

Feb        |         2

Mar        |         3

In the report view select month column, go to modeling tab and select sort column by month no. This will order months according to month no column.

Important things to remember

• If if a value is repeating it should have same coresponding integert value through out the table.
• if you create a new column with dax using calculations on Month column, you will not be able to sort month by that column.

Thanks

Vinayak

[Anonymous]

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

[kohlivinayak]

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]

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!

[v-yuezhe-msft]

@Anonymous, 

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


Regards,
Lydia

[Anonymous]

Hi @v-yuezhe-msft

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

[v-yuezhe-msft · Accepted solution]

@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

[Anonymous]

@v-yuezhe-msft

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

[v-yuezhe-msft]

@Anonymous，

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

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

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.

[v-yuezhe-msft]

@Anonymous，

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

Regards,
Lydia