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:
SWITCH( TRUE();
numberOfValues = 1 && orderClosed = "Yes";
IF( orderClosedDateContext <= chosenDate; 1; 0);
numberOfValues = 1 && orderClosed = "No";
IF( orderClosedDateContext > chosenDate; 1; 0);
1
)
Then I use this measure to filter the visual for showing only orders with value 1. Problem is that the orderClosedDate is shown fine for every order as long as I don't use this measure. But when I use this measure as a filter or just put it in the list as collumn, my orders are shown multiple times with every possible date. Like suddenly there is no relation anymore between the order and the orderClosedDate. Offcourse there isn't a direct relationship between these two tables (tabular won't allow that because I allready have a relation between my facttable and the OrderClosedDate-table). To make sure they should work together fine, I add a measure from my fact-table in the list.
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
