Forum Discussion
Using OR function in a DAX expression for a measure
Hello,
In my BI solution I have following tables:
Checking
| PK | ID | TYPE | A | B | C | D |
| 1 | 1 | T1 | 0 | 0 | 0 | 1 |
| 2 | 1 | T2 | 1 | 1 | 1 | 0 |
| 3 | 1 | T3 | 1 | 1 | 0 | 1 |
| 4 | 2 | T1 | 0 | 0 | 0 | 1 |
| 5 | 2 | T2 | 1 | 1 | 0 | 0 |
| 6 | 2 | T3 | 0 | 1 | 1 | 0 |
| 7 | 3 | T1 | 1 | 0 | 1 | 1 |
| 8 | 3 | T2 | 1 | 0 | 0 | 0 |
| 9 | 3 | T3 | 1 | 0 | 1 | 1 |
| 10 | 4 | T1 | 1 | 1 | 0 | 1 |
| 11 | 4 | T2 | 1 | 0 | 1 | 1 |
| 12 | 4 | T3 | 0 | 1 | 1 | 1 |
| 13 | 5 | T1 | 1 | 1 | 0 | 0 |
| 14 | 5 | T2 | 0 | 1 | 1 | 0 |
| 15 | 5 | T3 | 1 | 1 | 1 | 0 |
| 16 | 6 | T1 | 1 | 1 | 0 | 1 |
| 17 | 6 | T2 | 1 | 1 | 0 | 1 |
| 18 | 6 | T3 | 1 | 1 | 0 | 0 |
| 19 | 7 | T1 | 0 | 0 | 1 | 0 |
| 20 | 7 | T2 | 1 | 1 | 1 | 1 |
| 21 | 7 | T3 | 1 | 0 | 1 | 1 |
| 22 | 8 | T1 | 0 | 0 | 1 | 1 |
| 23 | 8 | T2 | 1 | 1 | 1 | 0 |
| 24 | 8 | T3 | 1 | 0 | 1 | 1 |
| 25 | 9 | T1 | 0 | 0 | 0 | 0 |
| 26 | 9 | T2 | 1 | 0 | 1 | 0 |
| 27 | 9 | T3 | 1 | 1 | 0 | 1 |
| 28 | 10 | T1 | 0 | 1 | 0 | 0 |
| 29 | 10 | T2 | 1 | 1 | 1 | 0 |
| 30 | 10 | T3 | 1 | 1 | 1 | 1 |
Number_Type
| PK | ID | TYPE | NUMBER |
| 1 | 1 | T1 | 4 |
| 2 | 1 | T2 | 23 |
| 3 | 1 | T3 | 20 |
| 4 | 2 | T1 | 2 |
| 5 | 2 | T2 | 19 |
| 6 | 2 | T3 | 18 |
| 7 | 3 | T1 | 3 |
| 8 | 3 | T2 | 19 |
| 9 | 3 | T3 | 31 |
| 10 | 4 | T1 | 4 |
| 11 | 4 | T2 | 18 |
| 12 | 4 | T3 | 9 |
| 13 | 5 | T1 | 3 |
| 14 | 5 | T2 | 17 |
| 15 | 5 | T3 | 7 |
| 16 | 6 | T1 | 0 |
| 17 | 6 | T2 | 20 |
| 18 | 6 | T3 | 26 |
| 19 | 7 | T1 | 3 |
| 20 | 7 | T2 | 20 |
| 21 | 7 | T3 | 15 |
| 22 | 8 | T1 | 3 |
| 23 | 8 | T2 | 19 |
| 24 | 8 | T3 | 19 |
| 25 | 9 | T1 | 0 |
| 26 | 9 | T2 | 20 |
| 27 | 9 | T3 | 2 |
| 28 | 10 | T1 | 4 |
| 29 | 10 | T2 | 18 |
| 30 | 10 | T3 | 12 |
Code_Descriptions
| PK | CODE_DESCRIPTION |
| 01 | Code A |
| 02 | Code B |
| 03 | Code C |
| 04 | Code D |
I created a merasures table MyMeasures with the measure Yes_No
Yes_No =
SWITCH( MIN(Code_Descriptions[PK]),
"01", IF(FIRSTNONBLANK(Checking[A], 1) = 1, 1, 0),
"02", IF(FIRSTNONBLANK(Checking[B], 1) = 1, 1, 0),
"03", IF(FIRSTNONBLANK(Checking[C], 1) = 1, 1, 0),
"04", IF(FIRSTNONBLANK(Checking[D], 1) = 1, 1, 0)
)
Following relationships apply to those table:
I'm using a slicer to select one value of Checking[ID] at a time
I added the following matrix:
Rows: Code_Descriptions[CODE_DESCRIPTION]
Values: MyMeasure[Yes_No]
The results are not what I want. I want an OR expression over the all the values of A that are not filtered out, same for B, C and D.
Example 1
Filter Checking[ID] = 1
that means the values filtered in table Checking are:
The result should be for
A: 0 OR 1 OR 1 = 1
B: 0 OR 1 OR 1 = 1
C: 0 OR 1 OR 0 = 1
D: 1 OR 0 OR 1 = 1
but I get this:
Example 2
Filter Checking[ID] = 5
that means the values filtered in table Checking are:
The result should be for
A: 1 OR 0 OR 1 = 1
B: 1 OR 1 OR 1 = 1
C: 0 OR 1 OR 1 = 1
D: 0 OR 0 OR 0 = 0
but I get this:
It's obvious that the measure MyMeasure[Yes_No] is not correct.
So how can I rewrite the measure in such a way that it does an OR function over the selected values of Checking[A], Checking[B], Checking[C] and Checking[D]?
Furthermore
I added a pie chart to the page.
Legend: Number_Type[TYPE]
Values: Number_Type[NUMBER]
If I select one or two of the types in the pie, it should filter the other type or types out and the OR measure should also be applicable to the remaining values of Checking[A], Checking[B], Checking[C] and Checking[D].
So how can I add an extra filter on table Checking by clicking on the pie?
I can provide the Booleans.pbix file with all the elements needed.
Thanks,
R.W.