IF OR when using COUNTROWS

Question

Hi,&nbsp;I'm need to modify the measure&nbsp;On Time = COUNTROWS(FILTER(OTIF,[ShiftInTime]=[TimeFrom]*[targetperc OT]))&nbsp;to alsoCOUNTROWS if&nbsp;[ShiftInTime]&lt;&gt;&nbsp;[TimeFrom]&nbsp;Thinking IF OR but don't know how.

chrisu · Answer

Would it work to add an OR (||) within the filter? It should count the rows where either of your tests are true.

On Time = COUNTROWS(FILTER(OTIF, [ShiftInTime]=[TimeFrom]*[targetperc OT] || [ShiftInTime]<> [TimeFrom]))

v-caliao-msft · Answer

android1
For CountRows, I don’t think it would work with the logical function, but we could use the logical function within the filter function.
&nbsp;
For the situation, if you would like to have both conditions work, we could write the formula in the following format:&nbsp;
On Time = COUNTROWS(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER( 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OTIF, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;And([ShiftInTime]=[TimeFrom]*[ta​rgetperc OT], [ShiftInTime]&lt;&gt;&nbsp;[TimeFrom]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )
&nbsp;
This works if both [TimeFrom]*[ta​rgetperc OT]&nbsp; And [ShiftInTime]&lt;&gt;&nbsp;[TimeFrom] are true;&nbsp;
The formula would be OK to write in the following way:&nbsp;
On Time = COUNTROWS(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER( 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OTIF, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[ShiftInTime]=[TimeFrom]*[ta​rgetperc OT]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [ShiftInTime]&lt;&gt;&nbsp;[TimeFrom]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;
&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
If you would like to use the Or function, use it in the following way:&nbsp;
On Time = COUNTROWS(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER( 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OTIF, 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OR([ShiftInTime]=[TimeFrom]*[ta​rgetperc OT], [ShiftInTime]&lt;&gt;&nbsp;[TimeFrom]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )
&nbsp;
The value is TRUE if any of the two arguments is TRUE; the value is FALSE if both the arguments are FALSE.&nbsp;
Adding the OR AND function reference:&nbsp;
OR Function (DAX)
AND Function (DAX)
&nbsp;
Any further assistance needed, please post back.
&nbsp;
Regards,
Charlie Liao

android1 · Answer

I feel your OR suggestion (the last one) should do what I need.

However, when I apply this to a slicer (OT %) and change the values, the results are not changed in the relevant columns.

I have a similar slicer called IF % which does change column values when a different option is selected.

Both use measures.

Here is my project -> https://www.dropbox.com/s/6ajjnyotics8vx9/OTIF%20Slicer%20Dilemma%20V2.0.pbix?dl=0

v-caliao-msft · Answer

I checked your data, the reason why the OT %won’t work, is because your measure having issue calculating OT(+ 15 -15):
&nbsp;
OT(+15-15) = 
COUNTROWS ( FILTER ( OTIF, OTIF[Punctuality] = "On Time" ) )
&nbsp;
[On time] is a measure,&nbsp; and should be write in a format of [On Time], which is the only measure that uses the OT % column; and OTIF[Punctuality] is in text type, when comparing those two values, it will generating errors under the visuals.
I think you might need to re-consider how to calculate the OT(+15-15) value here.

android1 · Answer

Hi Charlie Lao,&nbsp;I've changed my OT measure to&nbsp;OT(+15-15) = CALCULATE(COUNTROWS ( FILTER ( OTIF, OTIF[ShiftInTime] = OTIF[TimeFrom]*[targetperc OT]||OTIF[ShiftInTime] &lt;&gt; OTIF[TimeFrom]*[targetperc OT] )))&nbsp;Data still not changing when value selected in OT slicer. I was sure this would work.&nbsp;https://www.dropbox.com/s/r5uuuchxj1dx4zv/OTIF%20Sample.pbix?dl=0&nbsp;

Forum Discussion

IF OR when using COUNTROWS

5 Replies

Related content

COUNTROWS using date problem

COUNTROWS

Multiple Filters (COUNTROWS)

Using Countrows based on multiple conditions

Countrows without selection