Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
using which I was able to create dynamic measure.
Any help on this would be highly appreciated.
Thanks
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
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
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
using which I was able to create dynamic measure.
Any help on this would be highly appreciated.
Thanks
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |