IF OR when using COUNTROWS

[android1]

Hi,

I'm need to modify the measure On Time = COUNTROWS(FILTER(OTIF,[ShiftInTime]=[TimeFrom]*[targetperc OT])) to also

COUNTROWS if [ShiftInTime]<> [TimeFrom]

Thinking IF OR but don't know how.

[v-caliao-msft]

@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.

For the situation, if you would like to have both conditions work, we could write the formula in the following format: 

On Time = COUNTROWS(

                 FILTER(

                       OTIF,

                       And([ShiftInTime]=[TimeFrom]*[ta​rgetperc OT], [ShiftInTime]<> [TimeFrom]

                               )

                             ) 

                          )

This works if both [TimeFrom]*[ta​rgetperc OT]  And [ShiftInTime]<> [TimeFrom] are true; 

The formula would be OK to write in the following way: 

On Time = COUNTROWS(

               FILTER(

                 FILTER(

                       OTIF,

                       [ShiftInTime]=[TimeFrom]*[ta​rgetperc OT]

                              ),

                  [ShiftInTime]<> [TimeFrom]

                            )

                  ) 

If you would like to use the Or function, use it in the following way: 

On Time = COUNTROWS(

                 FILTER(

                       OTIF,

                       OR([ShiftInTime]=[TimeFrom]*[ta​rgetperc OT], [ShiftInTime]<> [TimeFrom]

                               )

                             ) 

                          )

The value is TRUE if any of the two arguments is TRUE; the value is FALSE if both the arguments are FALSE. 

Adding the OR AND function reference: 

OR Function (DAX)

AND Function (DAX)

Any further assistance needed, please post back.

Regards,

Charlie Liao

[android1]

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]

I checked your data, the reason why the OT %won’t work, is because your measure having issue calculating OT(+ 15 -15):

OT(+15-15) =

COUNTROWS ( FILTER ( OTIF, OTIF[Punctuality] = "On Time" ) )

[On time] is a measure,  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]

Hi Charlie Lao,

I've changed my OT measure to OT(+15-15) =
CALCULATE(COUNTROWS ( FILTER ( OTIF, OTIF[ShiftInTime] = OTIF[TimeFrom]*[targetperc OT]||OTIF[ShiftInTime] <> OTIF[TimeFrom]*[targetperc OT] )
))

Data still not changing when value selected in OT slicer. I was sure this would work.

https://www.dropbox.com/s/r5uuuchxj1dx4zv/OTIF%20Sample.pbix?dl=0

[chrisu]

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]))