Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Scatter plot doesn't match points rightly

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

1 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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

19 REPLIES 19
kohlivinayak
Resolver I
Resolver I

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
Not applicable

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

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
Not applicable

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.

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
Not applicable

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?

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
Not applicable

@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

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
Not applicable

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).

Screen Shot 2018-07-26 at 3.58.56 PM.png

 

 

 

 

 

 

(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
Not applicable

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@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

@Anonymous,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@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.

@Anonymous,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.