CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Naz
Naz

Reputation: 525

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

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:

enter image description here

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:

enter image description here

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!

Upvotes: 0

Views: 511

Answers (1)

Marcus
Marcus

Reputation: 541

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
)

Upvotes: 1

Related Questions