Dataset record datastamp

[Anonymous]

Hi,

my dataset has "CREATEDATETIME" column that contain a hierarchy data with  Year,Quarter,Month and Day.

In records you can also see hours, minutes and seconds but i can't use them because i don't see them in data field (right screen). Why?

How can i add them and maybe Week,Semester and Four Months?

Thanks

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Besides solution above, you could create calculated columns directly

year = YEAR([cretaedtime])

month = MONTH([cretaedtime])

day = DAY([cretaedtime])

weeknum = WEEKNUM([cretaedtime],2)

weekday = WEEKDAY([cretaedtime],2)

hour = HOUR([cretaedtime])

minute = MINUTE([cretaedtime])

seconds = SECOND([cretaedtime])

Best Regards
Maggie

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

[Anonymous]

Hi,

i created new colums like you, can i put these colums in CREATEDATETIME hierarchy?

[v-juanli-msft]

Hi @Anonymous 

As tested, i can't add "hours,,ect" columns in the hierarchy.

Based on my experience, it doesn't matter when i use them together as a whole hierarchy.

i can add them all in a matrix visual(add in Row field) or line/column/bar visual...(Add columns in X-axis)

Then expand all down to one level or expand to next level.

Best Regards
Maggie

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

[Anonymous]

Yes, i did that.

It's a bit confusing but it works, another question:

By deafult users see the talbe with hierarchy data, then they drill down until the last data (seconds)

Is it possibile let users choice how to see table?

Like modeling the talbe in power bi desktop, but on power bi service because they can't modify real report.

If users can'd do that we have to create a lot of page to let them see weekday x month, or weekday x hour etc...

Don't know if you understood me

[v-juanli-msft]

Hi @Anonymous 

Which is "weekday x month, or weekday x hour" ?

Do you mean drilling down to different level for each user? 

Or show different measures?

Based on my experience, we could create dynamic measures based on filter selection.

Do you want this function?

Please show more details for me to analyze.

Best Regards
Maggie

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

[Anonymous]

Hi,

i want that users can choice how to see table.

User 1 want "hour" on Y axis and "seconds" on X axis.

User 2 want "month" on Y axis and "year" on X axis.

Is it possible to do that?

Thanks

---

@v-juanli-msft wrote:

Hi @Anonymous 

Which is "weekday x month, or weekday x hour" ?

Do you mean drilling down to different level for each user? 

Or show different measures?

Based on my experience, we could create dynamic measures based on filter selection.

Do you want this function?

Please show more details for me to analyze.

Best Regards
Maggie

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

---

[v-juanli-msft]

Hi @Anonymous 

It seems possible.

Please create "year","month","seconds" in individual column, then try the methods as the following videos suggested:

Power BI - Dynamic Axis via Slicer (No DAX)

Create a dynamic bar chart axis in Power BI

Best Regards
Maggie

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

[v-juanli-msft]

Hi @Anonymous 

First of all, it should be noted that "hierarchy" here would not create time columns automatically.

You could add these columns in Edit Queries

With UI function as above, you could add "year","quarter",month","day","hour","minute","second",

As for weeknum and weekday, you could add a custom column in Edit Queries with the following function:

Date.DayOfWeek :Returns a number between 0 and 6 representing the day of the week in the provided datetime value

Date.WeekOfYear:Returns a number from 1 to 54 indicating which week of the year the date

You need click on "Apply&&Close" to save these changes into your model.

As for waht you said like "Semester and Four Months", we could create calcuated columns using DAX fuction.

If you have problem doing this, please provide clear syntax about them so i can figure out how to cretae this column.

Best Regards
Maggie

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