cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwadhwani Member
Member

Dynamic x-axis for DateTime component (Year,Quarter,Month,Day,Time)

Hello Experts,

 

Scenario: I have fact table f_WS with SalesValue,DateID,TimeID.

Dimension Date:  DateID, Date (dd-mm-yy).

Time Dimension: TimeID, Time (HH:MM: SS).

 

I want SalesValue against dynamically changing x-axis(Year,Quarter,Month,Date,Time). I have 4 slicers Year,Quarter,Month,Day.

My requirement is when I select Year 2017 from Year slicer my x-axis sholud show all the months of 2017 against SalesValue.

Similarly,When I select Quarter 3 from Quarer slicer x-axis should show July,Aug,Sept against SalesValue.

 

Approach 1:

I tried by creating one slicer table (SlicerID,Desc) with 5 rows. (1,Day) (2,Quarter), (3,Month), (4,Year), (5,Time).

Also, I added 3 columns to my fact table: FirstDayOfQuarter(dd-mm-yy), First Day of This Month(dd-mm-yy), FirstDayofYear(dd-mm-yy)

 

then I created one dynamic measure.

Dynamic Measure =
VAR SlicerID = MAX('Slicer'[ID])
RETURN
SWITCH(
TRUE() ,
SlicerID = 1 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Date[DateID],f_WS[DateID])),
SlicerID = 2 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Date[Date] ,f_WS[FirstDayOfQuarter])),
SlicerID = 3 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Date[Date] ,f_WS[First Day of This Month])),
SlicerID = 4 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Date[Date] ,f_WS[FirstDayofYear])),
SlicerID = 5 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Time[TimeID] ,f_WS[TimeID])),
0
)

 

On Bar Graph, on x-axis I have Date and on y-axis Dynamic measure. So when I select Year 2017 I see x-axis having all the months against dynamic measure. Similarly when I select month- July it shows all the days of July against dynamic measure.But when I select Day from the filter say 30 July it does not show dynamic measure against Time.It only shows data of selected day.

 

2nd Approach: I cross joined date and Time dimension named DateTime.Also created one column by concatenating Date and Time (name of column: key1) in fact table. Similarly one concatenated column in DateTime dimension named key. Then created following dynamic measure1:

Dynamic Measure1 =
VAR SlicerID = MAX('Slicer'[ID])
RETURN
SWITCH(
TRUE() ,
SlicerID = 1 , CALCULATE(sum(SalesValue),USERELATIONSHIP(DateTime[DateID],f_WS[DateID])),
SlicerID = 2 , CALCULATE(sum(SalesValue),USERELATIONSHIP(DateTime[Date] ,f_WS[FirstDayOfQuarter])),
SlicerID = 3 , CALCULATE(sum(SalesValue),USERELATIONSHIP(DateTime[Date] ,f_WS[First Day of This Month])),
SlicerID = 4 , CALCULATE(sum(SalesValue),USERELATIONSHIP(DateTime[Date] ,f_WS[FirstDayofYear])),
SlicerID = 5 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Time[key] ,f_WS[key1])), 
0
)

 

Again I am not getting Dynamic measure1 against time when I select Day form Day slicer. It only shows data of selected day.

 

Approach 3: I dragged  Date and Time on x-axis and Dynamic measure on Y-axis. I get till day level after that it gives option for drilling then it shows against time of that particular day.

Also I created hierarchy in DateTime till time level it gives data accurate but with drilling.

 

My requirement is when I select Day it should show dynamic measure against time.

 

@Thanks a lot @Phil_Seamark for your post on 

Dynamically Changing X Axis with respect to the Slicer Value Selected

  using which I was able to create dynamic measure. 

 

Any help on this would be highly appreciated.

 

Thanks

 

3 REPLIES 3
mwadhwani Member
Member

Dynamic x-axis for time component (Year,Quarter,Month,Day,Time)

Hello Experts,

 

Scenario: I have fact table f_WS with SalesValue,DateID,TimeID.

Dimension Date:  DateID, Date (dd-mm-yy).

Time Dimension: TimeID, Time (HH:MM: SS).

 

I want SalesValue against dynamically changing x-axis(Year,Quarter,Month,Date,Time). I have 4 slicers Year,Quarter,Month,Day.

My requirement is when I select Year 2017 from Year slicer my x-axis sholud show all the months of 2017 against SalesValue.

Similarly,When I select Quarter 3 from Quarer slicer x-axis should show July,Aug,Sept against SalesValue.

 

Approach 1:

I tried by creating one slicer table (SlicerID,Desc) with 5 rows. (1,Day) (2,Quarter), (3,Month), (4,Year), (5,Time).

Also, I added 3 columns to my fact table: FirstDayOfQuarter(dd-mm-yy), First Day of This Month(dd-mm-yy), FirstDayofYear(dd-mm-yy)

 

then I created one dynamic measure.

Dynamic Measure =
VAR SlicerID = MAX('Slicer'[ID])
RETURN
SWITCH(
TRUE() ,
SlicerID = 1 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Date[DateID],f_WS[DateID])),
SlicerID = 2 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Date[Date] ,f_WS[FirstDayOfQuarter])),
SlicerID = 3 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Date[Date] ,f_WS[First Day of This Month])),
SlicerID = 4 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Date[Date] ,f_WS[FirstDayofYear])),
SlicerID = 5 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Time[TimeID] ,f_WS[TimeID])),
0
)

 

On Bar Graph, on x-axis I have Date and on y-axis Dynamic measure. So when I select Year 2017 I see x-axis having all the months against dynamic measure. Similarly when I select month- July it shows all the days of July against dynamic measure.But when I select Day from the filter say 30 July it does not show dynamic measure against Time.It only shows data of selected day.

 

2nd Approach: I cross joined date and Time dimension named DateTime.Also created one column by concatenating Date and Time (name of column: key1) in fact table. Similarly one concatenated column in DateTime dimension named key. Then created following dynamic measure1:

Dynamic Measure1 =
VAR SlicerID = MAX('Slicer'[ID])
RETURN
SWITCH(
TRUE() ,
SlicerID = 1 , CALCULATE(sum(SalesValue),USERELATIONSHIP(DateTime[DateID],f_WS[DateID])),
SlicerID = 2 , CALCULATE(sum(SalesValue),USERELATIONSHIP(DateTime[Date] ,f_WS[FirstDayOfQuarter])),
SlicerID = 3 , CALCULATE(sum(SalesValue),USERELATIONSHIP(DateTime[Date] ,f_WS[First Day of This Month])),
SlicerID = 4 , CALCULATE(sum(SalesValue),USERELATIONSHIP(DateTime[Date] ,f_WS[FirstDayofYear])),
SlicerID = 5 , CALCULATE(sum(SalesValue),USERELATIONSHIP(Time[key] ,f_WS[key1])), 
0
)

 

Again I am not getting Dynamic measure1 against time when I select Day form Day slicer. It only shows data of selected day.

 

Approach 3: I dragged  Date and Time on x-axis and Dynamic measure on Y-axis. I get till day level after that it gives option for drilling then it shows against time of that particular day.

Also I created hierarchy in DateTime till time level it gives data accurate but with drilling.

 

My requirement is when I select Day it should show dynamic measure against time.

 

@Thanks a lot @Phil_Seamark for your post on 

Dynamically Changing X Axis with respect to the Slicer Value Selected

  using which I was able to create dynamic measure. 

 

Any help on this would be highly appreciated.

 

Thanks

 

v-jiascu-msft Super Contributor
Super Contributor

Re: Dynamic x-axis for DateTime component (Year,Quarter,Month,Day,Time)

Hi @mwadhwani,

 

I tried your Approach 1 but with no luck. The cause could be the time and the dates are in different tables. I also tried the method from this post. It's hard to handle time. Because there are 24 hours for everyday. Could you please share your sample?

 

Best Regards!

Dale

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

Re: Dynamic x-axis for DateTime component (Year,Quarter,Month,Day,Time)

Thanks @v-jiascu-msft for going through my long post and replying.

 

Below is the link for pbix file. Currently I have data only for July-17, Aug-17, Jul-16, Aug-16 and 1st September.

 

https://www.dropbox.com/s/vjwcbszor5m9kf5/Samplee.pbix?dl=0

 

Thanks

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 271 members 2,914 guests
Please welcome our newest community members: