Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using OR function in a DAX expression for a measure

Hello,

 

In my BI solution I have following tables:

Checking

PKIDTYPEABCD
11T10001
21T21110
31T31101
42T10001
52T21100
62T30110
73T11011
83T21000
93T31011
104T11101
114T21011
124T30111
135T11100
145T20110
155T31110
166T11101
176T21101
186T31100
197T10010
207T21111
217T31011
228T10011
238T21110
248T31011
259T10000
269T21010
279T31101
2810T10100
2910T21110
3010T31111

 

Number_Type

PKIDTYPENUMBER
11T14
21T223
31T320
42T12
52T219
62T318
73T13
83T219
93T331
104T14
114T218
124T39
135T13
145T217
155T37
166T10
176T220
186T326
197T13
207T220
217T315
228T13
238T219
248T319
259T10
269T220
279T32
2810T14
2910T218
3010T312

 

Code_Descriptions

PKCODE_DESCRIPTION
01Code A
02Code B
03Code C
04Code 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:
OR_Measure.jpg
 
 
 



















I'm using a slicer to select one value of Checking[ID] at a time

OR_Measure2.jpg
 
 
 
 
 
 
 
 
 
 
I added the following matrix:
OR_Measure3.jpg
 
 
 
 
 
 
 
 
 
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:
OR_Measure4.jpg
 
 
 
 
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
😧 1 OR 0 OR 1 = 1
but I get this:
 
OR_Measure5.jpg
 
 
 
 
 
 
Example 2
Filter Checking[ID] = 5
that means the values filtered in table Checking are:
OR_Measure6.jpg
 
 
 
 
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
😧 0 OR 0 OR 0 = 0
but I get this:
OR_Measure7.jpg
 
 
 
 
 
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.
 
OR_Measure8.jpg
 
 
 
 
 
 
 
 
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.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello,

 

I found a solution for the measurment.

 

Yes_No =
SWITCH( MIN(Code_Descriptions[PK]),
"01", IF(SUMX(Checking,Checking[A]) >= 1, 1, 0),
"02", IF(SUMX(Checking,Checking[B]) >= 1, 1, 0),
"03", IF(SUMX(Checking,Checking[C]) >= 1, 1, 0),
"04", IF(SUMX(Checking,Checking[D]) >= 1, 1, 0)
)
 
 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Hello,

 

I found a solution for the measurment.

 

Yes_No =
SWITCH( MIN(Code_Descriptions[PK]),
"01", IF(SUMX(Checking,Checking[A]) >= 1, 1, 0),
"02", IF(SUMX(Checking,Checking[B]) >= 1, 1, 0),
"03", IF(SUMX(Checking,Checking[C]) >= 1, 1, 0),
"04", IF(SUMX(Checking,Checking[D]) >= 1, 1, 0)
)
 
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.