Forum Discussion
Bar Chart Filter and X Axis Column Selection
Hi
I have a report that is using a Bar Chart Visualiser to present quantities on Y Axis and Date on the X Axis.
The I need to be able to change the date used on the X Axis between Order Entry Date and Delivery Date - pretty easy so far with a Parameter.
What I am struggling with is that the sales orders to be included in the bar chart fall within a 'Reporting Date Range' that is based on todays date -1 day and todays date + 5 days.
So when the user selects a date option from the Parameter Slicer need to filter Bar Chart based on the date field selected and the date range for the the selected field.
I have created 2 flag columns in the data source that calculates and presents whether a sales order Order Entry date falls within the Reporting Window and another that indicates if the delivery date falls within the reporting window.
Many Thanks
Hi Settz -Create a Parameter Table
eg: DateParam =
DATATABLE(
"Date Type", STRING,
{
{"Order Date"},
{"Delivery Date"}
}
)Now, create a measure that dynamically filters the data based on the selected date type and only includes rows in the reporting window.
ShowQty :=
VAR SelectedDateType = SELECTEDVALUE(DateParam[Date Type])
RETURN
CALCULATE(
SUM(Orders[Qty]),
FILTER(
Orders,
(SelectedDateType = "Order Date" && Orders[In Entry Window] = 1) ||
(SelectedDateType = "Delivery Date" && Orders[In Deliv Window] = 1)
)
)Create a calculated column or use a measure to switch the date used on the X-axis
SelectedDate :=
SWITCH(
SELECTEDVALUE(DateParam[Date Type]),
"Order Date", Orders[Order Date],
"Delivery Date", Orders[Delivery Date]
)you will need to create a disconnected calculated table or duplicate your date field and then filter it accordingly in your visual. Hope this works. please check
4 Replies
- rajendraongole1
Super User
Hi Settz -Create a Parameter Table
eg: DateParam =
DATATABLE(
"Date Type", STRING,
{
{"Order Date"},
{"Delivery Date"}
}
)Now, create a measure that dynamically filters the data based on the selected date type and only includes rows in the reporting window.
ShowQty :=
VAR SelectedDateType = SELECTEDVALUE(DateParam[Date Type])
RETURN
CALCULATE(
SUM(Orders[Qty]),
FILTER(
Orders,
(SelectedDateType = "Order Date" && Orders[In Entry Window] = 1) ||
(SelectedDateType = "Delivery Date" && Orders[In Deliv Window] = 1)
)
)Create a calculated column or use a measure to switch the date used on the X-axis
SelectedDate :=
SWITCH(
SELECTEDVALUE(DateParam[Date Type]),
"Order Date", Orders[Order Date],
"Delivery Date", Orders[Delivery Date]
)you will need to create a disconnected calculated table or duplicate your date field and then filter it accordingly in your visual. Hope this works. please check
Hi Settz ,
Just wanted to check if you had the opportunity to review the suggestion provided?If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi Settz ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi Settz
Just wanted to check if you had the opportunity to review the suggestion provided?If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
