Forum Discussion

b2wise's avatar
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 busin...
Need Help
  • v-xiaotang's avatar
    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.

mahoneypat's avatar
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's avatar
    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's avatar
      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?