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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mwadhwani
Kudo Kingpin
Kudo Kingpin

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
v-jiascu-msft
Employee
Employee

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.

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

mwadhwani
Kudo Kingpin
Kudo Kingpin

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.