Filter attribute in list

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

OrderLine

ListOfOptions

Calendar

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