Forum Discussion

b2wise

Helper III

5 years ago

Solved

Count With Multiple Conditions - DAX

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:

Need Help

v-xiaotang

5 years ago

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.

4 Replies

mahoneypat
Microsoft Employee
5 years ago
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
  Helper III
  5 years ago
  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
    Helper III
    5 years ago
    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?

v-xiaotang

Community Support

5 years ago

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.

Forum Discussion

Count With Multiple Conditions - DAX

4 Replies

Related content

Multiple conditions Index

Distinct Count with multiple conditions

Multiple Donut charts with conditions

Conditional Column with Multiple Conditions

Count rows with condition