Count With Multiple Conditions - DAX

[b2wise]

Hi,

I'm trying to count orders that must ship today using a DAX formula and am getting no results.

Must ship orders are determined by:

• Date created
• Time created
• Host priority

The business requirements are as follows: Count all orders with host priority 10-29 as long as they weren't created after 15:15 today and count all orders host priority 9 and lower as long as they weren't created after 16:15 today.

I made an order count formula:

Order Count = DISTINCTCOUNTNOBLANK(Headers[Fulfillment No])

And placed it in this formula:

Must Ship Orders =
CALCULATE (
[Order Count],
FILTER (
Headers,
SWITCH (
TRUE (),
Headers[Date Created] = MAX ( 'Current Date Time'[Date] )
&& ( Headers[Time Created] > "15:15" ), NOT ( Headers[Host Priority] >= "10" ),
Headers[Date Created] = MAX ( 'Current Date Time'[Date] )
&& ( Headers[Time Created] > "16:15" ), NOT ( Headers[Host Priority] < "10" )
)
)
)

What am I doing wrong?

Here is what my data looks like:

[v-xiaotang · Accepted solution]

Hi @b2wise 

I create a sample file under DQ mode, here is my operation steps.

-

Change the type of column [Date Created] to Date

Since type of your 3 columns is text and can’t be changed, so create the measures bellow.

sortbytime = //we need to create this measure so that we can sort by [Time Created], because type of this column is text
VAR _timecreated =
    SELECTEDVALUE ( Headers[Time Created] )
RETURN
    VALUE ( LEFT ( _timecreated, 2 ) ) * 100
        + VALUE ( MID ( _timecreated, 4, 2 ) )

get priority = //the reason to create this measure is same as the measure above
VAR _pri =
    SELECTEDVALUE ( Headers[Host Priority] )
VAR _lenvalue =
    IF ( LEN ( _pri ) > 1, LEN ( _pri ) - 1 )
VAR _getnumfromtext =
    RIGHT ( _pri, _lenvalue )
VAR _vau =
    IF ( ISERROR ( VALUE ( _pri ) ), _getnumfromtext, VALUE ( _pri ) )
RETURN
    _vau

then, create the measure count and put it in the Card visual.

count =
VAR _timecreated =
    SELECTEDVALUE ( Headers[Time Created] )
VAR _time =
    VALUE ( LEFT ( _timecreated, 2 ) ) * 100
        + VALUE ( MID ( _timecreated, 4, 2 ) )
VAR _count =
    CALCULATE (
        COUNTROWS ( Headers ),
        FILTER (
            ALL ( Headers ),
            (
                Headers[Date Created] < DATE ( 2021, 5, 25 )//I replace your MAX ( 'Current Date Time'[Date] ) with DATE ( 2021, 5, 25 ), because I dont know the structure of 'Current Date Time'[Date], you can change it back later.
                    && [get priority] >= 1
                    && [get priority] <= 29
            )
                || (
                    Headers[Date Created] = DATE ( 2021, 5, 25 )
                        && [get priority] >= 10
                        && [sortbytime] < 1515
                )
                || (
                    Headers[Date Created] = DATE ( 2021, 5, 25 )
                        && [get priority] < 10
                        && [sortbytime] < 1615
                )
        )
    )
RETURN
    _count

Result:

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You are going to want to change your data types first.  Your Created Date is datetime and should be date, and your time and priority columns are Text (those won't work well in expression where you use <, >, etc.  Once you do that, you can use an expression like this:

Must Ship Orders =
VAR maxdate =
    MAX ( 'Current Date Time'[Date] )
RETURN
    CALCULATE (
        [Order Count],
        Headers[Date Created] = maxdate,
        FILTER (
            ALL ( Headers[Time Created], Headers[Host Priority] ),
            OR (
                Headers[Time Created] > TIME ( 15, 15, 0 )
                    && Headers[Host Priority] <= 10,
                Headers[Time Created] > TIME ( 16, 15, 0 )
                    && Headers[Host Priority] > 10
            )
        )
    )

Are you sure you only want to count those created on the max date?

Pat

[b2wise]

Thanks for the answer.

I left out a few points:

A. This is in direct query so I don't think I can change the data types

B. The priority column occasionalty has text in it (e.g. P3) so I think I need to leave it as text

C.  I need to count all orders host priority 1 to 29 if date created is older than today, if date created is today count all orders created before 15:15 if host priority >= 10 and all orders created before 16:15 if host priority is <10

Thanks again for your help

[b2wise]

@mahoneypat sorry I'm new to the community and didn't realize you have to "@" someone so they see you replied. Can you see my last reply?