Forum Discussion
Filter attribute in list
Hello,
Whatever I try, I can't seem to get the following working.
I have a fact-table with all kinds of measures for orders. See this example:
Order OrderClosedDate DatePayment PricePaidWithoutTaxes PricePaidWithTaxes TotalPriceOrderWithTaxes
1 20200101 20191201 100 121 242
1 20200101 20191203 100 121 242
2 20191205 1000 1210 1210
I have an orders-table with attributes of the order. For example:
Order Name Type IndOrderClosed
1 Order 1 By phone yes
2 Order 2 By webshop no
I have a OrderClosedDates-table with all kinds of date-attributes that has a relationship with the collumn OrderClosedDate from the first table.
I have a Dates-table with the same kinds of date-attributes that has a relationship with the collumn DatePayment from the first table.
The first table also has a relationship with the second table: order = order.
In my pbix-file I have a list-visual that displays:
Ordername OrderclosedDate (from the OrderClosedDate-table) TotalPriceOrderWithTaxes
This visual is filtered by:
- DatePayment --> the user chooses a date, payments up and until that date, are shown in the visual (summed up by order). This filter has been taken care of in the measure TotalPriceOrderWithTaxes.
- Closed orders --> the user chooses yes and/or no.
- yes: only closed orders are shown
- no: only open orders are shown
- yes and no: all orders are shown
What I want to do is use the second filter (closed orders) not directly on the visual but use it to determine the following:
- If closed orders = yes than show all orders that have a OrderClosedDate up and until the chosen DatePayment.
So orders closed after the chosen DatePayment should be treated as if they weren't closed yet.
- If closed orders = no than show all orders that have no OrderClosedDate or have a ClosedOrderDate after the chosen DatePayment.
- If closed orders = yes and no or has no selection at all, than show all orders.
I tried to make a measure that determines:
- the chosen date (var chosenDate = VALUES(Dates[Date]))
- the orderClosedDate for the context-order (var orderClosedDateContext = MAX(OrderClosedDate[Date]))
I also tried this with VALUES instead of MAX but it doesn't work
- the chosen closedOrders-values (var chosenClosedOrders = VALUES(Order[IndOrderClosed])
I also tried this with CONCATENATEX to just put the values in a string. No luck there.
- the number of values chosen for closedOrders (var numberOfValues = DISTINCTCOUNT(Order[IndOrderClosed]))
Then I do this:
This is just an example for privacy reasons so it's not important why I want to do this or use this specific collumns. This is just a good example for what I'm trying to achieve with simular data. If anyone could help me figure this out, I would be very happy!
BK1234
Hi BK
Please consider this solution
The problem
========
You have a order header table with a status flag = open or closed.
You have a order line table with has a many:1 relationship with the order header table.
The order line table has a closed date and payment date column..
Your requirement is to have 2 slicers with payment date and order status with closed, open or both.
- If status slicer = closed than show all orders that have a closed date <= payment date slicer
- If status slicer = open than show all orders that have a closed date = blank or closed date > payment date slicer
- If status slicer = both than show all orders
The solution
=======
1) Add your tables
OrderHeader
OrderID
OrderChannel
OrderStatus
1
Phone
Open
2
Web
Closed
3
Mail Order
Open
4
Over counter
Closed
5
Phone
Open
6
Web
Closed
7
Mail Order
Open
8
Over counter
Closed
9
Web
Open
OrderLine
OrderID ClosedDat PaymentDate Qty 1 01/01/2020 01/02/2020 10 2 01/02/2020 01/01/2020 20 3 01/01/2020 30 4 01/01/2020 01/02/2020 40 4 01/01/2020 01/02/2020 50 4 01/01/2020 60 5 01/03/2020 70 5 01/03/2020 80 6 01/03/2020 01/04/2020 90 6 01/03/2020 01/04/2020 100 6 01/01/2020 01/04/2020 110 7 01/05/2020 120 8 01/05/2020 130 9 01/05/2020 01/05/2020 140 ListOfOptions
OptionID
OptionName
1
Closed not paid
2
Open not paid
3
Both
Calendar
Date
01/01/2019
etc, etc
31/12/2020
2) Add relationships
Add a 1:M relationship between OrderHeader and OrderLine.
But leave the Calendar table disconnected.
3) Add date slicer
Drag the Calendar date to a single drop down slicer (dont use a date range)
4) Add DAX measures:-
Total qty = SUM(OrderLine[Qty])
Closed not paid =
CALCULATE([Total qty],OrderHeader[OrderStatus]="Closed",
FILTER(OrderLine,OrderLine[ClosedDate]< MIN('Calendar'[Date])))
Open not paid =
CALCULATE([Total qty],OrderHeader[OrderStatus]="Open",
FILTER(OrderLine,OrderLine[ClosedDate] = BLANK() ||
OrderLine[ClosedDate] > MIN('Calendar'[Date])))
Selected qty =
SWITCH(
SELECTEDVALUE(ListOfOptions[OptionID]),
1,[Closed not paid],
2,[Open not paid],
3,[Total qty],
BLANK()
5) Add a table visual with
OrderHeader[OrderId]
Selected qty
6) Test and see if it works!
Try this OneDrive link to a PBIX example I created for you.
I will delete it after a few days.
4 Replies
- amitchandakSuper User
BK1234 , can put your data and output in table format. Not able to read that properly
- speedrampsSuper User
Hi BK
Please consider this solution
The problem
========
You have a order header table with a status flag = open or closed.
You have a order line table with has a many:1 relationship with the order header table.
The order line table has a closed date and payment date column..
Your requirement is to have 2 slicers with payment date and order status with closed, open or both.
- If status slicer = closed than show all orders that have a closed date <= payment date slicer
- If status slicer = open than show all orders that have a closed date = blank or closed date > payment date slicer
- If status slicer = both than show all orders
The solution
=======
1) Add your tables
OrderHeader
OrderID
OrderChannel
OrderStatus
1
Phone
Open
2
Web
Closed
3
Mail Order
Open
4
Over counter
Closed
5
Phone
Open
6
Web
Closed
7
Mail Order
Open
8
Over counter
Closed
9
Web
Open
OrderLine
OrderID ClosedDat PaymentDate Qty 1 01/01/2020 01/02/2020 10 2 01/02/2020 01/01/2020 20 3 01/01/2020 30 4 01/01/2020 01/02/2020 40 4 01/01/2020 01/02/2020 50 4 01/01/2020 60 5 01/03/2020 70 5 01/03/2020 80 6 01/03/2020 01/04/2020 90 6 01/03/2020 01/04/2020 100 6 01/01/2020 01/04/2020 110 7 01/05/2020 120 8 01/05/2020 130 9 01/05/2020 01/05/2020 140 ListOfOptions
OptionID
OptionName
1
Closed not paid
2
Open not paid
3
Both
Calendar
Date
01/01/2019
etc, etc
31/12/2020
2) Add relationships
Add a 1:M relationship between OrderHeader and OrderLine.
But leave the Calendar table disconnected.
3) Add date slicer
Drag the Calendar date to a single drop down slicer (dont use a date range)
4) Add DAX measures:-
Total qty = SUM(OrderLine[Qty])
Closed not paid =
CALCULATE([Total qty],OrderHeader[OrderStatus]="Closed",
FILTER(OrderLine,OrderLine[ClosedDate]< MIN('Calendar'[Date])))
Open not paid =
CALCULATE([Total qty],OrderHeader[OrderStatus]="Open",
FILTER(OrderLine,OrderLine[ClosedDate] = BLANK() ||
OrderLine[ClosedDate] > MIN('Calendar'[Date])))
Selected qty =
SWITCH(
SELECTEDVALUE(ListOfOptions[OptionID]),
1,[Closed not paid],
2,[Open not paid],
3,[Total qty],
BLANK()
5) Add a table visual with
OrderHeader[OrderId]
Selected qty
6) Test and see if it works!
Try this OneDrive link to a PBIX example I created for you.
I will delete it after a few days.
- BK1234Helper I
amitchandak I'm sorry but I don't know how to do that.
speedramps Thank you very much for your time and effort! With your solution my list now does what I want it to do :-D. We use unrelated date-tables all the time but with this report I haven't thought of it at all.
Thanks!
- speedrampsSuper User
Thank you BK1234 for the feedback. I am glad it worked ok and I enjoyed my spare time helping you during covid-19 lockdown.