Auto Date/time hierarchy not working, even though everything is set up

[sirski]

I'm just starting with Power BI and it's quite frustrating.

I connected a Table using MariaDB connector (After MySQL was not working, even though I installed latest connectors).

The table has a timestamp column named "time". Power BI knows it's a date/time column, and yet, it does not offer me to breakdown the column into a date format.

My goal is to plot a table with X axis showing days, but due to this non-user friendly interface, all I managed to do after hours of exploation is to just plot the data by timestamp, which is useless.

Please See the attached screenshot.

[Diogo_Dalla]

Hello,

You maybe using Direct Query. I manage to do that changing the queries to Import Mode.

[rusgesig]

Well if you don't even know how to use the query editor or add columns maybe you should start with an imported dataset and see if you can get the result that you want first.
According to these links you can create calculated columns and use the query editor for direct query.

https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages#:....

https://community.powerbi.com/t5/Desktop/How-to-create-Calculate-column-in-Direct-Query-Mode/m-p/874...

[rusgesig]

Great! In Query Editor you use 'M' not 'DAX', but don't worry you won't need to write any formulas.

Go on Add Column>Custom Column, then choose your date time column you want to use.

Then go to Transofrm and Split Column by delimiter.

That will give you three columns

Split the last column by delimeter and choose space

You will now have a year, month, day and time column

You can rename them as such, or you can further split the time column with delimter ':' for hours minutes and seconds.

Click Close and Apply top left, then you can add those new columns to a hierarchy like I showed you before.

You probably want to leave out seconds and just use minutes or hours.

[sirski]

none of this is working.  I could have also just duplicate the column and choose "date only". it gives a prompt: "This step results in a query that is not supported in DirectQuery mode". accordingly, it woulnd't let me apply it. I appreciate you trying to help but you can't recommend on actions if all this time you are only testing your suggestions from imported table mode.

[rusgesig]

Okay your 'last_active' column doesn't have a date hierarchy it appears, a column with a date hierarchy has a little calendar icon next to it.

If you drag that column into a table it should look like this:

Is your users_view table a local table or a direct query?
If it's a direct query you won't be able to edit the table, but you can get the columns you want and make your own table.

[sirski]

my use case it direct query because I'm trying to build a real time dashboard.

I tested with local table and manged to do this with:

New Column = 'users_view'[last_active].[Date]

but as you said, on direct query creating such columns leads to an error:

Column reference to 'last_active' in table 'users_view' cannot be used with a variation 'Date' because it does not have any.

How may I go about it then? This tool is so clumsy. 

[rusgesig]

Can you give me some examples of values for that column? I don't know what needs changing unless I see it. According to your first screenshot it is formatted as datetime, which is good.
For your 'New Column', change it to this and let's see what happens:

Date Value = DATEVALUE(users_view[last_active])
It should look something like this:

You can then format it to a date

Now that you have a date column you can begin building your hierarchy.
This is where you want a year, month, and day column, the DAX is Year = YEAR(users_view[last_active]). Just replace Year with Month and Day for the other two columns.

Once you've got those columns create a hierarchy:

a 'Year Hierarchy' will appear by the columns in your table on the right side of the screen.
Add, month and day to the hierarchy.

If you drag that hierarchy into a visual it will look like this:

This lets you drill up and down on visuals, or just expand the whole thing.

this is a month view (i've left it as numbers for a reason for now)
You can then drill down or expand the months to get the totals per day.

If you've gotten to this point you can extract time in the same way, add it to your hierarchy, then use the hierarchy as your date axis.

[sirski]

It doesn't work. 

If I test

Date Value = DATEVALUE(users_view[last_active])

on imported table,  I get error:

A single value for column 'last_active' in table 'users_view' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

And I I test it on Direct query table, there is no error, but the graph will not plot at all- getting can't display the visual. "Expression Error".

I don't know how you can provide screenshots of the table, if we both agree that on direct query mode the whole "Data" tab is not avaible for use.

My data format is a simple timestamp:

 

I wasted half a day already on microsoft's poorly made product. How do people have the energy to use this tool?

[rusgesig]

Hahaha it's honestly extremely versatile and powerful, I don't work with direct query since I use MySQL for warehousing data. But yeah, you definitely got off to a bad start. I did a bit of googling and it looks like you can use the query editor with direct query.

If you click on transform data it will open the query editor.

Can you get there?

[sirski]

I can. not sure though what syntax to use to add a new column?

[rusgesig]

Bruh sounds like you need to start with some basic tutorials. Columns can only be created in tables. I never said to use DATE() as a function, I told you you need to start with a date or datetime value (as a column, since you said you were able to do that) and then isolate the elements in order to reconstruct a hierarchy, by creating new columns based on your original date column. Your original date column should just  be the date not DAY(mytable[date]). 
If you want proper help you should provide more information on your data model since I have no idea what relationships you have going or if you even need any. I don't know what you are trying to display, and I don't know what field you are using to try and do so on a hierarchical date axis.

[sirski]

Here is another screenshot, more simple example

I have this table named users_view.

It has a column named "last active".

I want a line chart that shows how many users have logged in in the past day. I don't care about the exact time, just that it was in the past day.

As you can see in screenshot, I have two entries plotted separtely, one on 7:53 AM and other around 1 AM.

How do I make X axis to just show me a single date, so it will plot 2 entries on a single line?

I tried right clicking "users_view" table in fields, and create new column, but i don't understand what syntax to enter in order to generate the necessary column. 

[rusgesig]

Well the shortest way is to make columns for each level in the hierarchy.
Column1 shouldn't be Day it should be date.
So start with Column1 = mytable[datetime] (like how your header is formatted)
Then add columns you want in the hierarchy e.g.
Year= YEAR(mytable[datetime])
Month = MONTH(mytable[datetime])
Day = DAY(mytable[datetime])
Now you should have a column for Year, Month and Day (you can extend this to time as well)
Right click on year in your table editor and choose 'Create Hierarchy'. Then Add month and day to that hierarchy by right clicking on the columns.
Use this hierarchy you created as your x-axis, it should appear in your table like a column.

What you should actually do is have a calendar table with all your dates pegged to that.

[sirski]

I don't understand the steps.

What do you mean it should be date?

If I try : Column1 = DATE(mytable[time]) , the syntax is invalid, it says it requires 3 arguments.

Then you told me add column in  the hiereacy, how am I supposed to do that? by right clicking somewhere? on what?

And why should I start creating all these columns if I don't care about the year, month and day, but rather care about the date?

Power BI seems like a very frustrating tool.

[sirski]

Thanks for the effort but the problem was that I was using Direct Query.
Couldn't Microsoft just pop a message saying this feature is not availble if I use this method?

I managed to create a column using 

Column1 = DAY(mytable[time]) , but it is not what I Really need as it takes day of the month, regardless of month and year.

I just want to see for example a count of actions, in the last 30 days days, on a graph. How may I create a column that shows this simple thing? This is something that every web dashboard shows - 

For example, see how many notifications were sent in the last 30 days, broken down by DAY, even though the SQL timestamp column is a full timestamp with date and time, rather than date only.

[rusgesig]

Let me try again.

Firstly, I only use MYSQL DB's on PowerBI, it definitely works.

Next, a date hierarchy is 'Year>Quarter>Month>Day' no time by default.
Here is a formula to create a table:

_Timeinterval_CurrentMonth =

GENERATESERIES(

    EOMONTH(TODAY(),-1)+1,NOW() - 1/24/60, 1/24/60)

It looks like this: 

Here is a calendar table:

They are joined on the date column of both tables:

This is what a hierarchy looks like with the Date column from the Calendar, and the Time column from the Time series:

It's very hard to figure out what you want to do since you've censored everything in your screenshots.