Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

COUNTROW with a SWITCH case like condition?

I'm trying to get a COUNTROW for this use case, if it's a Type A order, I need to just count the ones within 70 days, otherwise, the ones 45 days and below.

The DAX formula below unfortunately counts both of them regardless of whether I apply a filter to the visual that will display it. How can I correct this? Thanks.

 

~Completion (Within Window) = 
    CALCULATE(
        COUNTROWS('Orders'),
        OR(
            AND('Orders'[Type] = "Type A", 'Work Orders'[CompletionTAT] <= 70),
            AND('Orders'[Type] = "Type B", 'Work Orders'[CompletionTAT] <= 45)
        )
    )

 

it

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, olimilo ;

    Please try it.

    Completion (Within Window) =
CALCULATE (
    COUNTROWS ( 'Orders' ),
    FILTER (
        'Orders',
        'Work Orders'[CompletionTAT] <> BLANK ()
            && OR (
                AND ( 'Orders'[Type] = "Type A", 'Work Orders'[CompletionTAT] <= 70 ),
                AND ( 'Orders'[Type] = "Type B", 'Work Orders'[CompletionTAT] <= 45 )
            )
    )
)

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    olimilo , Try like

    Completion (Within Window) =
    CALCULATE(
    COUNTROWS('Orders'),
    filter('Orders' OR(
    AND('Orders'[Type] = "Type A", 'Work Orders'[CompletionTAT] <= 70),
    AND('Orders'[Type] = "Type B", 'Work Orders'[CompletionTAT] <= 45)
    ) )
    )

     

    But this will work in CompletionTAT is a column, otherwise, we have the force some group bu context

    • olimilo's avatar
      olimilo
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Thanks Amit, this worked for me. How would I go about if I needed to add a condition to weed out blank values?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, olimilo ;

    Please try it.

    Completion (Within Window) =
CALCULATE (
    COUNTROWS ( 'Orders' ),
    FILTER (
        'Orders',
        'Work Orders'[CompletionTAT] <> BLANK ()
            && OR (
                AND ( 'Orders'[Type] = "Type A", 'Work Orders'[CompletionTAT] <= 70 ),
                AND ( 'Orders'[Type] = "Type B", 'Work Orders'[CompletionTAT] <= 45 )
            )
    )
)

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.