Forum Discussion

BK1234's avatar
BK1234
Helper I
6 years ago
Solved

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:

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

 

 

 

 

 

 

  • 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

    OrderIDClosedDatPaymentDateQty
    101/01/202001/02/202010
    201/02/202001/01/202020
    3 01/01/202030
    401/01/202001/02/202040
    401/01/202001/02/202050
    4 01/01/202060
    501/03/2020 70
    5 01/03/202080
    601/03/202001/04/202090
    601/03/202001/04/2020100
    601/01/202001/04/2020110
    7 01/05/2020120
    801/05/2020 130
    901/05/202001/05/2020140

     

    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.

    PBIX example report 

     

     

     

     

     

     

     

4 Replies

  • 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

    OrderIDClosedDatPaymentDateQty
    101/01/202001/02/202010
    201/02/202001/01/202020
    3 01/01/202030
    401/01/202001/02/202040
    401/01/202001/02/202050
    4 01/01/202060
    501/03/2020 70
    5 01/03/202080
    601/03/202001/04/202090
    601/03/202001/04/2020100
    601/01/202001/04/2020110
    7 01/05/2020120
    801/05/2020 130
    901/05/202001/05/2020140

     

    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.

    PBIX example report 

     

     

     

     

     

     

     

  • 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!

     

    • speedramps's avatar
      speedramps
      Super 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.