DAX - Filtering a measure - how to filter for two measure values, not one?

Question

I have the following 4 tables:

Customer
Lifts
Reports
Overdue

The data contained within each is:

Customer
Customer
A
B
C
D
E
F

Lifts
Lift ID       Lift Ref      Customer
1             Lift1         A
2             Lift2         A
3             Lift3         B         
4             Lift4         C
5             Lift5         D
6             Lift6         E
7             Lift7         E
8             Lift8         F
9             Lift9         F

Reports 
Lift ID       Report Date        Overdue 
1             01/01/19           Yes 
1             15/06/18           No 
1             12/04/18           Yes 
2             15/09/18           Yes 
2             08/12/18           No 
4             01/01/19           Yes 
6             01/02/19           No

Overdue
Overdue
Yes
No
No Report

Overdue is a disconnected table - it is not connected to any other table

The relationships are:

Customer 1<>* Lifts
Lifts  1<>*  Reports

I have a slicer on the report for Customer and Overdue - these are both multi select slicers

I have a table visual added to the report canvas. I need it to display the latest report date and associated Overdue value for every lift (after slicers have filtered the data) - if there is no report for the lift selected then return 'No Report' for the Overdue value and leave the Report Date as blank

If I select all customers except Customer F from customer slicer, and Yes/No Report from the Overdue slicer, I should get the following in my table visual:

Lift ID           Lift Ref           Overdue      Report Date
1                 Lift1              Yes          01/01/19
3                 Lift3              No Report    
4                 Lift4              Yes          01/01/19
5                 Lift5              No Report    
7                 Lift7              No Report

However what I am actually getting is:

If I select all customers except Customer F from customer slicer, and Yes/No from the Overdue slicer, I should get the following in my table visual:

Lift ID           Lift Ref           Overdue      Report Date
1                 Lift1              Yes          01/01/19
2                 Lift2              No           08/12/18    
4                 Lift4              Yes          01/01/19
6                 Lift6              No           01/02/19

However what I am actually getting is:

The below is my PBIX file:

https://1drv.ms/u/s!AuiIgc_S9J5JhbYL_-CFbONVzOLWDA

I can add the measures here that I am using for MRepDate and MOverdue but dont want to make this post too long!

Hopefully someone can help with this as it is driving me mad!

Do your measures only return True/False? If not then, what do you mean by MeasureNo AND MeasureNoReport? — Alexis Olson
MeasureNo and MeasureYes are measure names - so the IF statement is returning measures based on slicer selection — Naz
I understand that they are measures. You didn't answer what I asked. If the measures return numbers instead of Booleans, I don't know what you expect MeasureNo AND MeasureNoReport to return. — Alexis Olson
Sorry, they return either dates or BLANK - I have amended my question to clarify this further. Cheers — Naz
OK. What does Date1 AND Date2 mean? Is it somehow a date or a string or what? — Alexis Olson

Marcus Marcus · Accepted Answer · 2019-02-13T20:03:44

A better approach would be to use the SWITCH function, with the condition being TRUE().

In the below example, the switch would go though each test, till it hits one which evaluates to true then returns that result. The default case is optional

= SWITCH ( TRUE(),
Logical Test 1, result 1,
Logical Test 2, result 2,
Logical Test 3, result 3,
Default Case
)

DAX - Filtering a measure - how to filter for two measure values, not one?

1 Answers